如何在MySQL中以多对多关系将逗号分隔列表中的一个字段中的数据连接起来? [英] How to concatenate data from one field, in a comma-delimited list, in a many-to-many relationship in MySQL?

问题描述

我和人与部门之间有多对多的关系，因为一个人可以在许多部门中.

I have a many-to-many relationship between People and Departments since one person can be in many departments.

People          Departments
------          -----------
pID  pName      deptID   deptName
1    James      1        Engineering
2    Mary       2        Research
3    Paul       3        Marketing
                4        Communications

People_Departments
------------------
pID   deptID
1     1
1     2
2     2
2     4
3     1
3     2
3     3

这是我想要的:

pName  deptName
James  Engineering, Research
Mary   Research, Communication
Paul   Engineering, Research, Marketing

如果我使用下面的SQL在表上进行普通的左联接，我将获得与一个人相关的几行:

If I do plain LEFT JOINs on the tables using the SQL below, I will get several rows related to one person:

SELECT people.pName,
       departments.deptName
FROM people
LEFT JOIN people_departments ON people.pID=people_departments.pID
LEFT JOIN departments ON people_departments.deptID=departments.deptID

我尝试了GROUP_CONCAT的各种组合，但是没有运气.

I have tried various combinations of GROUP_CONCAT but without luck.

有什么想法可以分享吗?

Any ideas to share?

推荐答案

    SELECT people.pName,
           GROUP_CONCAT(departments.deptName SEPARATOR ', ') deptName
      FROM people
 LEFT JOIN people_departments 
        ON people.pID = people_departments.pID
INNER JOIN departments 
        ON people_departments.deptID = departments.deptID
  GROUP BY people.pID

输出:

+-------+----------------------------------+
| pName | deptName                         |
+-------+----------------------------------+
| James | Engineering, Research            |
| Mary  | Research, Communications         |
| Paul  | Engineering, Research, Marketing |
+-------+----------------------------------+
3 rows in set (0.00 sec)

这篇关于如何在MySQL中以多对多关系将逗号分隔列表中的一个字段中的数据连接起来?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！