[译] 使用MySQL GROUP_CONCAT()创建逗号分隔的列表

在这篇博文中，我们将学习如何使用MySQL GROUP_CONCAT()函数将一个表按照某一列进行分组并且用逗号分隔。
 
我会使用 ‘group_member’ 表来查询数据和做演示示例。 group_member表有4个不同的组:100、200、300和400。

 我们如果需要把上表按照member_name的首字母来排序且放到各自的组里面，可以使用GROUP_CONCAT()函数，并且用group_num来进行分组，同时指定逗号作为分隔。

 如下：

 结果如下：

译者注：
 测试sql如下：

CREATE TABLE group_member(group_num int,member_name varchar(20));
insert into group_member values (100,'Josh');
insert into group_member values (100,'Andy');
insert into group_member values (100,'Sally');
insert into group_member values (200,'Beth');
insert into group_member values (200,'Rick');
insert into group_member values (300,'Harry');
insert into group_member values (400,'Dorothy');
insert into group_member values (400,'Johhny');
insert into group_member values (400,'Kimberly');
insert into group_member values (400,'Wilson');

mysql> select * from group_member;
+-----------+-------------+
| group_num | member_name |
+-----------+-------------+
|       100 | Josh        |
|       100 | Andy        |
|       100 | Sally       |
|       200 | Beth        |
|       200 | Rick        |
|       300 | Harry       |
|       400 | Dorothy     |
|       400 | Johhny      |
|       400 | Kimberly    |
|       400 | Wilson      |
+-----------+-------------+
10 rows in set (0.00 sec)
SELECT group_num,
	   GROUP_CONCAT(member_name
					ORDER BY member_name ASC
					SEPARATOR ',') AS member_list
FROM group_member
GROUP BY group_num;
+-----------+--------------------------------+
| group_num | member_list                    |
+-----------+--------------------------------+
|       100 | Andy,Josh,Sally                |
|       200 | Beth,Rick                      |
|       300 | Harry                          |
|       400 | Dorothy,Johhny,Kimberly,Wilson |
+-----------+--------------------------------+
4 rows in set (0.00 sec)
'''
复制