主要记录自己工作中,解决一下负责查询或特殊业务处理的sql语句,持续更新。。。
1.情况一
(1).主表订单表 order
id | order_number | description |
123456 | 订单一 | |
123457 | 订单二 |
(2).食物表food,(food原名称,new_food修改后名称)
id | order_id | food | new_food |
122 | 123456 | 红烧鸡腿 | null |
123 | 123456 | 红烧鸡腿 | 照烧鸡腿 |
124 | 123456 | 炭烤鸡腿 | 照烧鸡腿 |
125 | 123456 | 炭烤鸡腿 | null |
(3).饮料表drinks,(drinks原名称,new_drinks修改后名称)
id | order_id | drinks | new_drinks |
211 | 123456 | 冰红茶 | null |
212 | 123456 | 冰红茶 | 可乐 |
213 | 123456 | 绿茶 | 可乐 |
214 | 123456 | 绿茶 | null |
最终结果
id | order_number | food | drink |
123456 | 订单一 | 红烧鸡腿、炭烤鸡腿、照烧鸡腿 | 冰红茶、可乐、绿茶 |
SELECT a.id,
a.order_number,
b.food,
b.drinks
FROM order a
LEFT JOIN (SELECT f.issue_order_id,
GROUP_CONCAT(DISTINCT IFNULL( f.new_food, f.food )) AS food,
GROUP_CONCAT(DISTINCT IFNULL( d.new_drinks, d.drinks )) AS drinks
FROM food f
LEFT JOIN drinks d ON f.order_id = d.order_id
GROUP BY f.order_id) b ON a.id = b.order_id