这期内容当中小编将会给大家带来有关怎么在mysql中使用多个left join连接查询，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

mysql查询时需要连接多个表时，比如查询订单的商品表，需要查询商品的其他信息，其他信息不在订单的商品表，需要连接其他库的表，但是连接的条件基本都是商品ID就可以了，先给一个错误语句（查询之间的嵌套，效率很低）：

SELECTA.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_nameFROM(SELECTA.sub_order_idASorder_id,A.photo_idASwid,A.photo_nameASwork_name,A.supply_price,A.sell_price,sum(A.num)AStotal_num,(A.sell_price-A.supply_price)ASsell_profit,(A.sell_price-A.supply_price)/A.sell_priceASsell_percent,A.goods_id,A.goods_name,B.goods_nameASclassifyFROMorder_goodsASALEFTJOIN(SELECTA.goods_id,A.parentid,B.goods_nameFROMtest_qyg_goods.goodsASALEFTJOINtest_qyg_goods.goodsASBONA.parentid=B.goods_id)ASBONA.goods_id=B.goods_idWHEREA.createtime>='2016-09-0900:00:00'ANDA.createtime<='2016-10-1623:59:59'ANDFROM_UNIXTIME(UNIX_TIMESTAMP(A.createtime),'%Y-%m-%d')!='2016-09-28'ANDFROM_UNIXTIME(UNIX_TIMESTAMP(A.createtime),'%Y-%m-%d')!='2016-10-07'GROUPBYA.photo_idORDERBYA.goods_idASC)ASALEFTJOIN(SELECTA.wid,A.brand_id,B.gb_nameFROMtest_qyg_user.buser_goods_listASALEFTJOINtest_qyg_supplier.brandsASBONA.brand_id=B.gbid)ASBONA.wid=B.wid

查询结果耗时4秒多，explain分析，发现其中2个子查询是全部扫描，可以使用mysql的多个left join优化

SELECTA.sub_order_id,A.photo_idASwid,A.photo_nameASwork_name,A.supply_price,A.sell_price,sum(A.num)AStotal_num,(A.sell_price-A.supply_price)ASsell_profit,(A.sell_price-A.supply_price)/A.sell_priceASsell_percent,A.goods_id,A.goods_name,B.parentid,C.goods_nameASclassify,D.brand_id,E.gb_name,sum(CASEWHENF.buy_type='yes'THENA.numELSE0END)AStotal_buy_num,sum(CASEWHENF.buy_type='yes'THENA.numELSE0END*A.sell_price)AStotal_buy_money,sum(CASEWHENF.buy_type='no'THENA.numELSE0END)AStotal_give_num,sum(CASEWHENF.buy_type='no'THENA.numELSE0END*A.sell_price)AStotal_give_moneyFROMorder_goodsASALEFTJOINtest_qyg_goods.goodsASBONA.goods_id=B.goods_idLEFTJOINtest_qyg_goods.goodsASCONB.parentid=C.goods_idLEFTJOINtest_qyg_user.buser_goods_listASDONA.photo_id=D.widLEFTJOINtest_qyg_supplier.brandsASEOND.brand_id=E.gbidLEFTJOINorder_info_subASFONA.sub_order_id=F.order_idWHEREA.createtime>='2016-09-0900:00:00'ANDA.createtime<='2016-10-1623:59:59'ANDFROM_UNIXTIME(UNIX_TIMESTAMP(A.createtime),'%Y-%m-%d')!='2016-09-28'ANDFROM_UNIXTIME(UNIX_TIMESTAMP(A.createtime),'%Y-%m-%d')!='2016-10-07'GROUPBYA.photo_idORDERBYA.goods_idASC

查询结果耗时0.04秒

上述就是小编为大家分享的怎么在mysql中使用多个left join连接查询了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注亿速云行业资讯频道。