mysql多個left join連接查詢用法分析
本文實例講述了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分析,發(fā)現(xiàn)其中2個子查詢是全部掃描,可以使用mysql的多個left join
優(yōu)化
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常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數(shù)據庫鎖相關技巧匯總》
希望本文所述對大家MySQL數(shù)據庫計有所幫助。
- 圖文詳解Mysql使用left?join寫查詢語句執(zhí)行很慢問題的解決
- mysql使用left?join連接出現(xiàn)重復問題的記錄
- MySQL中多個left?join?on關聯(lián)條件的順序說明
- 關于mysql?left?join?查詢慢時間長的踩坑總結
- MYSQL Left Join優(yōu)化(10秒優(yōu)化到20毫秒內)
- 解決Mysql的left join無效及使用的注意事項說明
- mysql left join快速轉inner join的過程
- mysql高效查詢left join和group by(加索引)
- 詳解mysql 使用left join添加where條件的問題分析
- mysql中l(wèi)eft join設置條件在on與where時的用法區(qū)別分析
- MySQL 8.0.18 Hash Join不支持left/right join左右連接問題
- MySQL left join操作中on和where放置條件的區(qū)別介紹
- MySQL利用profile分析慢sql詳解(group left join效率高于子查詢)
- MySQL在右表數(shù)據不唯一的情況下使用left join的方法
- MySQL表LEFT JOIN左連接與RIGHT JOIN右連接的實例教程
- mysql left join的基本用法以及on與where的區(qū)別
相關文章
關于MySQL innodb_autoinc_lock_mode介紹
下面小編就為大家?guī)硪黄P于MySQL innodb_autoinc_lock_mode介紹。小編覺得挺不錯的,現(xiàn)在就分享給大家,也給大家做個參考。一起跟隨小編過來看看吧2017-03-03