MySQL查询以显示较低版本的MySQL上用逗号分隔的数据

Question

我有一张这样的桌子

CREATE TABLE `users_search_activity` (
  `ID` bigint(20) UNSIGNED NOT NULL,
  `user_id` int(11) NOT NULL,
  `country_id` int(11) NOT NULL,
  `search_keywords` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users_search_activity`
--

INSERT INTO `users_search_activity` (`ID`, `user_id`, `country_id`, `search_keywords`, `date`) VALUES
(1, 132, 2, 'xavie', '2021-07-13 08:20:37'),
(2, 132, 6, 'xavier', '2021-07-13 08:21:38'),
(3, 132, 5, 'xavier ins', '2021-07-13 08:21:39'),
(4, 132, 4, 'xavier ins', '2021-07-13 08:21:39'),
(5, 131, 9, 'xavier ins',  '2021-07-13 08:22:12'),
(6, 132, 7, 'xavier ins', '2021-07-13 08:22:25'),
(7, 132, 8, 'xavier ins', '2021-07-13 09:24:43'),
(8, 132, 6, 'xavier ins', '2021-07-13 09:24:45'),
(9, 132, 4, 'xavier insa', '2021-07-13 09:24:47'),
(10, 131, 5, 'ins', '2021-07-13 09:24:54'),
(11, 132, 3, 'ins', '2021-07-13 09:24:54'),
(12, 132, 2, 'ins', '2021-07-13 09:24:58'),
(13, 132, 9, 'ins', '2021-07-13 09:24:59'),
(14, 132, 0, 'ins', '2021-07-13 09:25:00'),
(15, 132, 0, 'ins', '2021-07-13 09:25:02'),
(16, 132, 0, 'inst', '2021-07-13 09:58:20'),
(17, 132, 0, 'inst', '2021-07-04 09:58:25'),
(18, 132, 0, 'inst', '2021-07-07 09:58:25'),
(19, 132, 0, 'inst', '2021-07-11 09:58:26'),
(20, 1, 12, 'University Business Academy in Novi Sad', '2021-07-14 10:16:33');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `users_search_activity`
--
ALTER TABLE `users_search_activity`
  ADD PRIMARY KEY (`ID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `users_search_activity`
--
ALTER TABLE `users_search_activity`
  MODIFY `ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;
COMMIT; 

现在，我想从哪里通过country_id和date查询数据组。因此，我提出了这样的疑问

SELECT COUNT(*) OVER (PARTITION BY country_id, DATE(date)) AS count, 
   user_id, country_id, DATE(date) as date, group_concat(search_keywords) FROM users_search_activity
   group by user_id,country_id,DATE(date)

这是一个很好的MySQL 8+，但是有人能告诉我如何使这个查询也适用于旧版本的MySQL吗？

任何帮助和建议都将是非常感谢的。

以下是我在使用MySQL 5.7尝试查询时遇到的错误

19 errors were found during analysis.

An alias was previously found. (near "count" at position 62)
An alias was expected. (near " " at position 61)
Unrecognized keyword. (near "count" at position 62)
Unexpected token. (near "," at position 67)
Unexpected token. (near "user_id" at position 74)
Unexpected token. (near "," at position 81)
Unexpected token. (near "country_id" at position 83)
Unexpected token. (near "," at position 93)
Unrecognized keyword. (near "DATE" at position 95)
Unexpected token. (near "(" at position 99)
Unrecognized keyword. (near "date" at position 100)
Unexpected token. (near ")" at position 104)
Unrecognized keyword. (near "as" at position 106)
Unrecognized keyword. (near "date" at position 109)
Unexpected token. (near "," at position 113)
Unrecognized keyword. (near "group_concat" at position 115)
Unexpected token. (near "(" at position 127)
Unexpected token. (near "search_keywords" at position 128)
Unexpected token. (near ")" at position 143)

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY country_id, DATE(date)) AS count,
   user_id, country_id, DATE(d' at line 1

但是在MySQL 8.1中，我没有任何错误。

Answer 1

使用关联子查询：

SELECT ( SELECT COUNT(*)
         FROM users_search_activity usa_sq
         WHERE (usa.country_id, DATE(usa.date)) = (usa_sq.country_id, DATE(usa_sq.date))
         ) AS count, 
       user_id, 
       country_id, 
       DATE(date) as date, 
       GROUP_CONCAT(search_keywords) 
FROM users_search_activity usa
GROUP BY 1,2,3,4
ORDER BY 2,3,4

https://dbfiddle.uk/?rdbms=mysql_8.0&rdbms2=mysql_5.7&fiddle=9a49f48ca0663b3dd050753f63ca6dd1