我在人员和部门之间存在多对多关系,因为一个人可以在多个部门。
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
的各种组合,但没有运气。
有什么想法可以分享吗?
【问题讨论】:
【参考方案1】: 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)
【讨论】:
实际上,我的组合都不起作用,因为我省略了非常重要的 GROUP BY。我在实际项目中从几个多对多相关的表中获取数据,并在您的帮助下设法获得了我想要的数据。再次感谢! 请注意,在此查询中,people_departments
的 LEFT JOIN 将被转换为 INNER JOIN,因为在 INNER JOIN 的 ON 子句中使用了people_departments.deptID
。如果你想列出没有部门的人,两个 JOIN 都应该是 LEFT JOIN。
@PaulSpiegel 很好。更新了查询。【参考方案2】:
我的解决办法是:
SELECT people.pName,
GROUP_CONCAT(tmp.deptName SEPARATOR ', ') deptName
FROM people
LEFT JOIN (SELECT people_departments.pID, departments.deptName FROM people_departments LEFT JOIN departments ON people_departments.deptID = departments.deptID) as tmp
ON tmp.pID = people.pID
GROUP BY people.pID
结果:
+-------+----------------------------------+
| pName | deptName |
+-------+----------------------------------+
| James | Engineering, Research |
| Mary | Research, Communications |
| Paul | Engineering, Research, Marketing |
+-------+----------------------------------+
【讨论】: