如何使用Group by和Concate与多个记录对单个id - How to use Group by and Concate with multiple records against single id

How to use Group by and Concate with multiple records against single id

我有3张表

组
group_members

我将显示所有学生的数据与他们加入的组名表。可能是学生加入了许多组，我想在一个表列中用逗号显示他们的组名。

这是我的SQL代码:

SELECT `users`.*,`groups`.`gr_name`,`id` as `uid` FROM `users` 
LEFT JOIN `group_members` ON `users`.`id` = `group_members`.`gm_telebe` 
LEFT JOIN `groups` ON `group_members`.`gm_group` = `groups`.`gr_id` 
WHERE `users`.`level` < 5 GROUP by `id` 
ORDER by `reg_date` DESC;

如果我删除GROUP by id，那么我将得到所有结果与重复的表行。但是我想把每个学生的名字显示一次。

你能指出我在这段代码中的错误吗?

GROUP_CONCAT将以逗号分隔的值连接在一起。

下面是修改后的查询:

SELECT `users`.*, GROUP_CONCAT(`groups`.`gr_name`) AS group_name, `id` as `uid`
FROM `users` LEFT JOIN `group_members` ON `users`.`id` = `group_members`.`gm_telebe`
LEFT JOIN `groups` ON `group_members`.`gm_group` = `groups`.`gr_id`
WHERE `users`.`level` < 5 GROUP by `id` ORDER by `reg_date` DESC;

参考:http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html function_group-concat