mysql - 查询以逗号分隔列表显示一列名称

Question

我在 mysql 数据库中工作，我需要一些帮助来列出要显示为逗号分隔的一列数据值，实际上有两个表:一个是人员表，另一个是组表，现在一个人可以属于许多组，所以我试图显示用户数据。

我想这样显示数据:

UserName                 Group            Email
User #1                  Grp1,Grp3,Grp5   user1@email.com
User #2                  Grp3,Grp1        user2@email.com

这是我的查询

select ft_smsender_groups as g left join (select p1.id,p2.group_id 
from ft_smsender_persons2groups as p2 inner join ft_smsender_persons 
as p1 on p1.id = p2.person_id ) as p2 on g.id = p2.group_id','g.id,g.name,
created,updated where 
deleted = 0 group by id,name

实际上涉及到三个表:

ft_smsender_groups
ft_smsender_persons2groups - This table holds key from both the tables
ft_smsender_persons

Answer 1

您可以使用 group_concat功能:

SELECT   p.name, p.email, GROUP_CONCAT(g.name) AS groups
FROM     ft_smsender_persons AS p
JOIN     ft_smsender_persons2groups AS p2g ON p.id = p2g.person_id 
JOIN     ft_smsender_groups AS g ON g.id = p2g.group_id
WHERE    deleted = 0
GROUP BY p.name, p.email