group concat函数

编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。每个日期的销售产品名称应按词典序排列。返回按sell_date排序的结果表。

原始表格：

activities
sell_date	product
2020-05-30	Headphone
2020-06-01	Pencil
2020-06-02	Mask
2020-05-30	Basketball
2020-06-01	Bible
2020-06-02	Mask
2020-05-30	T-Shirt

--分组聚合，去重，排序

SELECT sell_date, count(product) AS num_sold, group_concat(DISTINCT product ORDER BY product) AS products FROM activities a GROUP BY sell_date ;

sell_date	num_sold	products
2020-05-30	3	Basketball,Headphone,T-Shirt
2020-06-01	2	Bible,Pencil
2020-06-02	2	Mask

--类别间更改分隔符

SELECT sell_date, count(product) AS num_sold, group_concat(DISTINCT product ORDER BY product SEPARATOR ' ') AS products FROM activities a GROUP BY sell_date ;

sell_date	num_sold	products
2020-05-30	3	Basketball Headphone T-Shirt
2020-06-01	2	Bible Pencil
2020-06-02	2	Mask