php - 如何从具有一对多关系的表中选择一行结果

Question

我有 2 个表:

餐厅

+---------------+----------------+
| restaurant_id | Restaurant name|
+---------------+----------------+
| 1             | KFC            |
| 2             | McD            |
+----+---------------------------+

美食

+---------------+---------------+----------------+
| cuisine_id    | restaurant_id | cuisine        |
+---------------+---------------+----------------+
| 1             | 1             | Fastfood       |
| 2             | 1             | Fried Chicken  |
| 3             | 2             | Fastfood       |
| 4             | 2             | Burger         |
+---------------+---------------+----------------+

是否可以得到这样的数据=

+---------------+----------------+------------------------+
| restaurant_id | Restaurant name| Cuisine                |
+---------------+----------------+------------------------+
| 1             | KFC            |Fastfood, Fried Chicken |
| 2             | McD            |Fastfood, Burger        |
+----+---------------------------+------------------------+

用一个查询？或者我应该通过 php 来完成(先选择表 Restaurant > foreach 循环 > 按 Restaurant id 选择 Cuisine > 解析为新数组)。

Answer 1

您可以使用 GROUP_CONCAT为此:

SELECT r.restaurant_id, r.Restaurant_name,
       GROUP_CONCAT(c.cuisine ORDER BY c.cuisine)
FROM Restaurant AS r
LEFT JOIN Cuisine AS c ON r.restaurant_id = c.restaurant_id
GROUP BY  r.restaurant_id, r.Restaurant_name

注意:GROUP_CONCAT 中的ORDER BY 子句只有在您想要有序 逗号分隔列表时才需要菜式 名称。