MySQL GROUP_CONCAT函数的使用 2022/09/14

🔥group by应用场景🔥

使用group by可以分组统计每个部门有多少员工。假如，除了统计每个部门的员工数量之外，还想知道具体是哪些员工（员工列表），又该如何实现呢？

id	name	dept
1	张三	部门A
2	王二	部门B
3	大哥	部门A
4	小弟	部门B
5	小妹	部门A

变成

dept	count	name_list
部门A	3	张三、大哥、小妹
部门B	2	王二、小弟

🔥 group_concat的使用 🔥

group_concat配合group by一起使用，用于将某一列的值按指定的分隔符进行拼接，MySQL默认的分隔符为逗号。

group_concat语法

group_concat ([ distinct ] column_name [ order by column_name asc / desc ] [separator '分隔符' ] )

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

select dept,group_concat(name) from employee group by dept;
+-------+--------------------+
| dept  | group_concat(name) |
+-------+--------------------+
| 部门A | 张三               |
| 部门B | 李四,张小妹        |
+-------+--------------------+
2 rows in set (0.01 sec)

select * from employee;
+----+--------+------+--------+-------+
| id | name   | sex  | salary | dept  |
+----+--------+------+--------+-------+
|  1 | 张三   | 男   |   5500 | 部门A |
|  2 | 李四   | 男   |   4500 | 部门B |
|  3 | 张小妹 | 女   |   4500 | 部门B |
+----+--------+------+--------+-------+
3 rows in set (0.00 sec)

select dept,count(*) from employee group by dept;
+-------+----------+
| dept  | count(*) |
+-------+----------+
| 部门A |        1 |
| 部门B |        2 |
+-------+----------+
2 rows in set (0.00 sec)

select dept,count(*),group_concat(name) from employee group by dept;
+-------+----------+--------------------+
| dept  | count(*) | group_concat(name) |
+-------+----------+--------------------+
| 部门A |        1 | 张三               |
| 部门B |        2 | 李四,张小妹        |
+-------+----------+--------------------+
2 rows in set (0.00 sec)

select dept,count(*),group_concat(name order by name desc) from employee group by dept;
+-------+----------+---------------------------------------+
| dept  | count(*) | group_concat(name order by name desc) |
+-------+----------+---------------------------------------+
| 部门A |        1 | 张三                                  |
| 部门B |        2 | 李四,张小妹                           |
+-------+----------+---------------------------------------+
2 rows in set (0.00 sec)