MySQL查询，显示不同列之间以逗号和组分隔的数据

Question

我有一个MySQL表，我存储了所有的用户搜索。表格看起来像这样

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和日期获得数据组。因此，我的查询语句如下

WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY country_id, DATE(date) ORDER BY ID) rn, COUNT(*) OVER (PARTITION BY country_id, DATE(date)) cnt FROM users_search_activity ) SELECT ID, cnt AS count, search_keywords, user_id, country_id, DATE(date) as date FROM cte WHERE rn = 1;

除了search_keywords之外，这里工作得很好。它只显示单个search_keywords。我想一个接一个地显示它们，用逗号分隔给定日期和country_id。

谁能告诉我怎么做?任何帮助或建议都是非常感激的。谢谢你,

输出应该像这样

count  user_id  country_id  date         search_keywords
1     132       4          2021-07-13  xavier ins, xavier insa

Answer 1

可以使用group_concat

WITH cte AS ( 
SELECT *,DATE(date),ROW_NUMBER() OVER (PARTITION BY country_id, DATE(date) ORDER BY ID) rn, 
COUNT(*) OVER (PARTITION BY country_id, DATE(date)) cnt 
FROM users_search_activity 
) 
,tab2 as (
select t1.country_id,
date(date) dat,
group_concat(t1.search_keywords)
from cte t1
group by t1.country_id,
date(date)
)
SELECT *
FROM cte t1,
tab2 t2
WHERE t1.rn = 1
and t1.country_id = t2.country_id
and DATE(t1.date) = t2.dat
;

更新日期:2021/8/3

5.7也有group_concat，你只需要处理row_number。

select t1.country_id,
date(date) dat,
group_concat(t1.search_keywords),
max(case when t1.row_number = 1 then t1.user_id else null end) user_id
from (
select t1.*,
@rn := case when @temp1 is null then 1
when @temp1 = t1.country_id and @temp2 = DATE(date) then 0
else 1
end row_number,
@temp1:= t1.country_id,
@temp2:= DATE(date)
from users_search_activity t1,(select @rn:=0, @temp1:='', @temp2:='') t2
order by country_id, DATE(date), t1.id
) t1
group by t1.country_id,
date(date)
;

Answer 2

首先感谢您以文本格式提供如此详细的信息。你的问题值得点赞。

您不需要任何排序函数，如row_number()或cte。使用group_concat()进行简单分组就足够了。

查询:

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)

输出:| |计数user_id | country_id | |日期group_concat (search_keywords)
|----: | ------: | ---------: | :--------- | :--------------------------------------| 1 | 132 | 0 | 2021-07-04 |本月
| 1 | 132 | 0 | 2021-07-07 |本月
| 1 | 132 | 0 | 2021-07-11 |本月
| 1 | 132 | 0 | 2021-07-13 |本月、ins ins
| 1 | 132 | 2 | 2021-07-13 | xavie, ins
| 1 | 132 | 3 | 2021-07-13 | ins
| 1 | 132 | 4 | 2021-07-13 | xavier早期,泽维尔ins
| 2 | 131 | 5 | 2021-07-13 | ins
| 2 | 132 | 5 | 2021-07-13 |xavier ins
| 1 | 132 | 6 | 20121-07-13 | xavier ins,xavier
| 1 | 132 | 7 | 20121-07-13 | xavier ins
| 1 | 132 | 8 | 20121-07-13 | xavier ins
| 2 | 131 | 9 | 20121-07-13 | xavier ins
| 2 | 132 | 9 | 20121-07-13 | ins
| 1 | 1 | 12 | 20121-07-14 |诺维萨德大学商学院

db<此处小提琴>