mysql多表 子查詢 左連接

問題：要做一個mysql的複雜查詢 多表查詢 且包含 子連接
解決：
select c.*,d1.debt,d2.hasorders from dw_customerinfo c left join (select d.customerid, sum(d.price)>sum(d.pay) debt from dw_ordersdetail d where d.state='1' and d.pubtime<= now() group by d.customerid) d1 on c.id=d1.customerid
left join (select d.customerid, count(d.customerid)>0 hasorders from dw_ordersdetail d group by d.customerid) d2 on c.id=d2.customerid
方案1：ps:  測試未通過
Select * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
方案2：ps：測試通過
SELECT artists.Artist, cds.title, genres.genre
FROM cds
LEFT JOIN genres N cds.genreID = genres.genreID
LEFT JOIN artists ON cds.artistID = artists.artistID;
參考資料：
Mysql 多表聯合查詢效率分析
MySQL多表連接查詢Left Join,Right Join
分享到：

2010-10-15 10:46
瀏覽 5573
分類:非技術
評論