如何在MySQL中以多对多关系从逗号分隔列表中的一个字段连接数据?

我和人与部门之间存在多对多关系,因为一个人可以在许多部门中工作。

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在表上进行普通的LEFT JOIN连接,我将获得与一个人相关的几行:

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但没有运气。

有什么想法要分享吗?

回答:

    SELECT people.pName,

GROUP_CONCAT(departments.deptName SEPARATOR ', ') deptName

FROM people

LEFT JOIN people_departments

ON people.pID = people_departments.pID

LEFT 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中以多对多关系从逗号分隔列表中的一个字段连接数据? 的全部内容, 来源链接: utcz.com/qa/430383.html

回到顶部