mysql多个left join连接查询用法分析
程序员文章站
2022-03-12 19:45:51
本文实例讲述了mysql多个left join连接查询用法。分享给大家供大家参考,具体如下:
mysql查询时需要连接多个表时,比如查询订单的商品表,需要查询商品的其他信...
本文实例讲述了mysql多个left join连接查询用法。分享给大家供大家参考,具体如下:
mysql查询时需要连接多个表时,比如查询订单的商品表,需要查询商品的其他信息,其他信息不在订单的商品表,需要连接其他库的表,但是连接的条件基本都是商品id就可以了,先给一个错误语句(查询之间的嵌套,效率很低):
select a.order_id, a.wid, a.work_name, a.supply_price, a.sell_price, a.total_num, a.sell_profit, a.sell_percent, a.goods_id, a.goods_name, a.classify, b.gb_name from ( select a.sub_order_id as order_id, a.photo_id as wid, a.photo_name as work_name, a.supply_price, a.sell_price, sum(a.num) as total_num, ( a.sell_price - a.supply_price ) as sell_profit, ( a.sell_price - a.supply_price ) / a.sell_price as sell_percent, a.goods_id, a.goods_name, b.goods_name as classify from order_goods as a left join ( select a.goods_id, a.parentid, b.goods_name from test_qyg_goods.goods as a left join test_qyg_goods.goods as b on a.parentid = b.goods_id ) as b on a.goods_id = b.goods_id where a.createtime >= '2016-09-09 00:00:00' and a.createtime <= '2016-10-16 23:59:59' and from_unixtime( unix_timestamp(a.createtime), '%y-%m-%d' ) != '2016-09-28' and from_unixtime( unix_timestamp(a.createtime), '%y-%m-%d' ) != '2016-10-07' group by a.photo_id order by a.goods_id asc ) as a left join ( select a.wid, a.brand_id, b.gb_name from test_qyg_user.buser_goods_list as a left join test_qyg_supplier.brands as b on a.brand_id = b.gbid ) as b on a.wid = b.wid
查询结果耗时4秒多,explain分析,发现其中2个子查询是全部扫描,可以使用mysql的多个left join
优化
select a.sub_order_id, a.photo_id as wid, a.photo_name as work_name, a.supply_price, a.sell_price, sum(a.num) as total_num, ( a.sell_price - a.supply_price ) as sell_profit, ( a.sell_price - a.supply_price ) / a.sell_price as sell_percent, a.goods_id, a.goods_name, b.parentid, c.goods_name as classify, d.brand_id, e.gb_name, sum( case when f.buy_type = 'yes' then a.num else 0 end ) as total_buy_num, sum( case when f.buy_type = 'yes' then a.num else 0 end * a.sell_price ) as total_buy_money, sum( case when f.buy_type = 'no' then a.num else 0 end ) as total_give_num, sum( case when f.buy_type = 'no' then a.num else 0 end * a.sell_price ) as total_give_money from order_goods as a left join test_qyg_goods.goods as b on a.goods_id = b.goods_id left join test_qyg_goods.goods as c on b.parentid = c.goods_id left join test_qyg_user.buser_goods_list as d on a.photo_id = d.wid left join test_qyg_supplier.brands as e on d.brand_id = e.gbid left join order_info_sub as f on a.sub_order_id = f.order_id where a.createtime >= '2016-09-09 00:00:00' and a.createtime <= '2016-10-16 23:59:59' and from_unixtime( unix_timestamp(a.createtime), '%y-%m-%d' ) != '2016-09-28' and from_unixtime( unix_timestamp(a.createtime), '%y-%m-%d' ) != '2016-10-07' group by a.photo_id order by a.goods_id asc
查询结果耗时0.04秒
更多关于mysql相关内容感兴趣的读者可查看本站专题:《mysql常用函数大汇总》、《mysql日志操作技巧大全》、《mysql事务操作技巧汇总》、《mysql存储过程技巧大全》及《mysql数据库锁相关技巧汇总》
希望本文所述对大家mysql数据库计有所帮助。
推荐阅读
-
MySQL利用profile分析慢sql详解(group left join效率高于子查询)
-
MySQL连接查询INNER JOIN、LEFT JOIN、RIGHT JOIN
-
mysql中left join设置条件在on与where时的用法区别分析
-
mysql多个left join连接查询用法分析
-
MYSQl left join 联合查询效率分析_MySQL
-
MYSQl left join 联合查询效率分析_MySQL
-
MySQL left join 联合查询的效率分析
-
mysql left join用法分析
-
mysql left join用法分析
-
MYSQl left join联合查询效率分析_MySQL