我有两个表:DISH和DISH_HAS_DISHES。Dish表包含所有菜肴,“ Dish_has_dishes”表与“ 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
这里的meat_id和dish_id都是来自DISH表的ID。现在我想要这样的格式:
meal_id dish_ids dish_names 1 2,3,4 dish_2, dish_3, dish_4
这是用逗号分隔的菜肴ID和每餐的名称。怎么做?
使用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