mysql - 在 SQL 中，有没有办法将表变成列表？

Question

我有这样的表格:

mysql> select id, name from wp_bp_groups limit 5;
+----+-----------------------------------------+
| id | name                                    |
+----+-----------------------------------------+
|  3 | Some Group Here                         |
| 11 | Another Group Here                      |
| 29 | Groupy Groupy Groupy Group              |
| 39 | Some Other Other Group Goes Here        |
|  7 | Yep, Here's a Test Group                |
+----+-----------------------------------------+
5 rows in set (0.02 sec)

mysql> select group_id, user_id from wp_bp_groups_members limit 5;
+----------+---------+
| group_id | user_id |
+----------+---------+
|       17 |      71 |
|       24 |     209 |
|       22 |     175 |
|       17 |     200 |
|       17 |     102 |
+----------+---------+

最后，我想得到一个列出组中所有成员的数组，所以我有一个连接它们的 SQL 查询，如下所示:

SELECT wp_bp_groups.name, wp_bp_groups_members.user_id FROM wp_bp_groups LEFT JOIN wp_bp_groups_members ON wp_bp_groups.id = wp_bp_groups_members.group_id; 

输出是这样的:

mysql> SELECT wp_bp_groups.name, wp_bp_groups_members.user_id from wp_bp_groups left join wp_bp_groups_members on wp_bp_groups.id = wp_bp_groups_members.group_id limit 5;
+------------------+---------+
| name             | user_id |
+------------------+---------+
| Test Group       |       1 |
| Test Group       |     206 |
| Test Group       |      24 |
| Test Group       |      47 |
| Test Group       |      52 |
+------------------+---------+
5 rows in set (0.02 sec)

但理想情况下，我希望它返回更像这样的东西:

name: Test Group
user_ids: 1, 206, 24, 47, 52

有办法吗？

Answer 1

在 MySQL 中，您可以使用函数 group_concat 将每个组中的所有元素串联起来。所以你必须对你的结果进行分组。因为你想得到你可以查询的所有用户ID

SELECT wp_bp_groups.name, GROUP_CONCAT(wp_bp_groups_members.user_id) as user_ids FROM wp_bp_groups LEFT JOIN wp_bp_groups_members ON wp_bp_groups.id = wp_bp_groups_members.group_id group by wp_bp_groups.name; 

结果应该是

+------------------+----------------------+
| name             | user_ids             |
+------------------+----------------------+
| Test Group       |       1,206,24,47,52 |
+------------------+----------------------+