MySQL:多行以逗号分隔单行 [英] MySQL : Multiple row as comma separated single row
本文介绍了MySQL:多行以逗号分隔单行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个表:DISH和DISH_HAS_DISHES。菜盘有所有菜,Dish_has_dishes表与菜表具有一对多的关系。也就是说一个菜可以有多个菜。例如
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_id dish_name
1 dish_1
2 dish_2
3 dish_3
4 dish_4
DISH_HAS_DISHES :
meal_id dish_id
1 2
1 3
1 4
这里,meal_id和dish_id都是DISH表中的ID。现在我想要这样的格式:
Here meal_id and dish_id both are IDs from DISH table. Now I want a format like this:
meal_id dish_ids dish_names
1 2,3,4 dish_2, dish_3, dish_4
这是以逗号分隔的菜单ID和每餐的名称。如何做?
That is comma separated dish id and names for each meal. How to do that?
推荐答案
使用GROUP_CONCAT FUNCTION
Use GROUP_CONCAT FUNCTION
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
这篇关于MySQL:多行以逗号分隔单行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文