MySQL左连接多对一

问题描述

为简化我的问题:假设我有3张桌子.

To simplify my problem: Let's say I have 3 tables.

Rooms              People                    Things
--------           --------                  --------
id| name           id | name | fk_rooms      id | name | fk_rooms
-----------        ---------------------     ---------------------
1 | kitchen        1  | John | 1              1 | TV   | 2
2 | bedroom        2  | Mary | 2              2 | bed  | 2
                   3  | Andy | 1              3 | sink | 1
                   4  | Laura| 1

现在我正在做类似的事情:

Now I'm doing something like:

SELECT r.name AS room_name, p.name AS name, t.name AS thing FROM Rooms r
LEFT JOIN People p ON p.fk_rooms = r.id
LEFT JOIN Things t ON t.fk_rooms = r.id

在我的情况下，它很完美，除了少数几个与"Rooms"表具有多对一关系.因此，我希望只接收两行，而不是结果集中的新行与"Rooms"表具有不同的"People"和"Things"名称.

which in my case works perfectly except for a few that have many to one relationship with the "Rooms" table. So instead of new rows in the result set holding the different names for "People" and "Things" in relation to the "Rooms" table, I would like to receive only two rows:

1. kitchen, John, Andy, Laura, sink
2. bedroom, Mary, TV, bed

r.id上的

A GROUP BY将从每个表中仅select行.非常感谢您的帮助！

A GROUP BY on r.id will only select one row from each table. Any help is highly appreciated!

推荐答案

这是您要查找的内容:

SELECT r.name AS room_name,
   GROUP_CONCAT(p.name separator ',') AS people_name,
   GROUP_CONCAT(t.name separator ',') AS things
FROM Rooms r
LEFT JOIN People p ON p.fk_rooms = r.id
LEFT JOIN Things t ON t.fk_rooms = r.id
GROUP BY r.id

这篇关于MySQL左连接多对一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！