mysql多個(gè)left join連接查詢(xún)用法分析
本文實(shí)例講述了mysql多個(gè)left join連接查詢(xún)用法。分享給大家供大家參考,具體如下:
mysql查詢(xún)時(shí)需要連接多個(gè)表時(shí),比如查詢(xún)訂單的商品表,需要查詢(xún)商品的其他信息,其他信息不在訂單的商品表,需要連接其他庫(kù)的表,但是連接的條件基本都是商品ID就可以了,先給一個(gè)錯(cuò)誤語(yǔ)句(查詢(xún)之間的嵌套,效率很低):
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
查詢(xún)結(jié)果耗時(shí)4秒多,explain分析,發(fā)現(xiàn)其中2個(gè)子查詢(xún)是全部掃描,可以使用mysql的多個(gè)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
查詢(xún)結(jié)果耗時(shí)0.04秒
更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專(zhuān)題:《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》及《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》
希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。
- 圖文詳解Mysql使用left?join寫(xiě)查詢(xún)語(yǔ)句執(zhí)行很慢問(wèn)題的解決
- mysql使用left?join連接出現(xiàn)重復(fù)問(wèn)題的記錄
- MySQL中多個(gè)left?join?on關(guān)聯(lián)條件的順序說(shuō)明
- 關(guān)于mysql?left?join?查詢(xún)慢時(shí)間長(zhǎng)的踩坑總結(jié)
- MYSQL Left Join優(yōu)化(10秒優(yōu)化到20毫秒內(nèi))
- 解決Mysql的left join無(wú)效及使用的注意事項(xiàng)說(shuō)明
- mysql left join快速轉(zhuǎn)inner join的過(guò)程
- mysql高效查詢(xún)left join和group by(加索引)
- 詳解mysql 使用left join添加where條件的問(wèn)題分析
- mysql中l(wèi)eft join設(shè)置條件在on與where時(shí)的用法區(qū)別分析
- MySQL 8.0.18 Hash Join不支持left/right join左右連接問(wèn)題
- MySQL left join操作中on和where放置條件的區(qū)別介紹
- MySQL利用profile分析慢sql詳解(group left join效率高于子查詢(xún))
- MySQL在右表數(shù)據(jù)不唯一的情況下使用left join的方法
- MySQL表LEFT JOIN左連接與RIGHT JOIN右連接的實(shí)例教程
- mysql left join的基本用法以及on與where的區(qū)別
相關(guān)文章
Navicat配置mysql數(shù)據(jù)庫(kù)用戶(hù)權(quán)限問(wèn)題
這篇文章主要介紹了Navicat配置mysql數(shù)據(jù)庫(kù)用戶(hù)權(quán)限問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-03-03springboot啟動(dòng)404問(wèn)題以及解決方案
這篇文章主要介紹了springboot啟動(dòng)404問(wèn)題以及解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-01-01MySQL外鍵關(guān)聯(lián)操作的實(shí)現(xiàn)
本文主要介紹了MySQL外鍵關(guān)聯(lián)操作的實(shí)現(xiàn),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2022-07-07關(guān)于MySQL innodb_autoinc_lock_mode介紹
下面小編就為大家?guī)?lái)一篇關(guān)于MySQL innodb_autoinc_lock_mode介紹。小編覺(jué)得挺不錯(cuò)的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-03-03