语法
将多行单元格内容合并到一起。
group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )
题目:统计每天出售的物品种类和数量。
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 |
+------------+-------------+
Result 表:
+------------+----------+------------------------------+
| sell_date | num_sold | products |
+------------+----------+------------------------------+
| 2020-05-30 | 3 | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2 | Bible,Pencil |
| 2020-06-02 | 1 | Mask |
+------------+----------+------------------------------+
对于2020-05-30,出售的物品是 (Headphone, Basketball, T-shirt),按词典序排列,并用逗号 ',' 分隔。
对于2020-06-01,出售的物品是 (Pencil, Bible),按词典序排列,并用逗号分隔。
对于2020-06-02,出售的物品是 (Mask),只需返回该物品名。
答案
- 使用
count()
计数 - 使用
group_concat
选择需要合并的字段,按照ASC排序,并且选择分隔符separator是逗号, - 使用
Group by
按sell_date分组
select sell_date, count(distinct product) num_sold,
group_concat(distinct product order by product asc separator ',') as products
from activities
group by sell_date
简略写法:不写分隔符,默认就是逗号。
# Write your MySQL query statement below
select sell_date, count(distinct product) num_sold,
group_concat(distinct product) as products
from activities
group by sell_date
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/group-sold-products-by-the-date