MySQL：多行作为逗号分隔的单行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3492539/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL : Multiple row as comma separated single row
提问by Swar
I have two tables : DISH and DISH_HAS_DISHES. Dish table has all the dishes and "Dish_has_dishes" table has a one-to-many relationship with "Dish" table. I.e. a dish can have multiple dishes. For example
我有两个表：DISH 和 DISH_HAS_DISHES。Dish 表包含所有的菜肴，“Dish_has_dishes”表与“Dish”表是一对多的关系。即一道菜可以有多个菜。例如
DISH:
菜:
dish_id dish_name
1 dish_1
2 dish_2
3 dish_3
4 dish_4
DISH_HAS_DISHES:
DISH_HAS_DISHES:
meal_id dish_id
1 2
1 3
1 4
Here meal_id and dish_id both are IDs from DISH table. Now I want a format like this:
这里的meal_id 和dish_id 都是来自DISH 表的ID。现在我想要这样的格式：
meal_id dish_ids dish_names
1 2,3,4 dish_2, dish_3, dish_4
That is comma separated dish id and names for each meal. How to do that?
这是每顿饭的逗号分隔的菜名和名称。怎么做？
回答by Michael Pakhantsov
Use GROUP_CONCAT FUNCTION
使用 GROUP_CONCAT 函数
http://dev.mysql.com/tech-resources/articles/4.1/grab-bag.html
http://dev.mysql.com/tech-resources/articles/4.1/grab-bag.html
SELEct m.meal_Id,
GROUP_CONCAT(dish_id) dish_ids,
GROUP_CONCAT(dish_name) dish_names
FROM DISH_HAS_DISHES m JOIN DISH d ON (m.dish_id = d.dish_id)
GROUP BY meal_Id
