MySQL左连接多对一行

Question

为了简化我的问题：假设我有三个表。

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

现在我正在进行类似以下的操作：

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

在我的情况下，这个方案非常完美，但对于一些与“房间”表存在多对一关系的内容却不行。所以，我希望结果集中的新行只包含与“房间”表相关的“人员”和“物品”的不同名称，而不是每个名称都有新行。

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

在 r.id 上的 GROUP BY 只会从每个表中选择一行。非常感谢您的帮助！

- NewInTheBusiness

Answer 1

以下是您正在寻找的内容：

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

- medina

---

谢谢！经过一些修改，我成功让它工作了，因为实际查询非常复杂。 - NewInTheBusiness

我花了好几个小时，只是因为我忘记分组了。谢谢。 - karliwson

Answer 2

是的，你需要使用group by子句，并且需要使用GROUP_CONCAT函数。你应该按People.fk_rooms和Thing.fk_rooms对结果进行分组。

也许你可以使用两个不同的查询：第一个查询将会得到Rooms和People的连接，按fk_rooms分组，选择了三列，它们是RoomsID、RoomName、People；而第二个查询将会得到Rooms和Thing的连接，按fk_rooms分组，选择了三列，它们是RoomID、RoomName、Things。在你的查询中，你将这些选择命名为t1和t2，通过RoomsID连接t1和t2，选择t1.RoomName、t1.People、t2.Things。

祝你好运。

- Lajos Arpad