如何在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 deptNameJames 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