MySql的join(連線)查詢 (三表 left join 寫法)

神馬和浮雲發表於2014-03-28

1、內連線:將兩個表中存在連結關係的欄位符合連線條件的記錄形成記錄集

Select A.name,B.name from A inner join B on A.id=B.id和

Select A.name,B.name from A,B where A.id=B.id結果是一樣的(內連線的inner關鍵字可省略);

2、外連線:分為左外連線和右外連線

左連線A、B表結果包括A的全部記錄和符合條件的B的記錄。

右聯結A、B表的結果和左聯結B、A的結果是一樣的,也就是說:

Select A.name,B.name from A Left Join B on A.id=B.id和

Select A.name,B.name from B Right Join A on B.id-A.id執行後的結果是一樣的。

3、全聯結

4、無聯結

5、三表聯結查詢

select username,psw,gname,tel from (t1 left join t2 on t1.t1_id=t2.t1_id) left join t3 on t1.t1_id=t3.t1_id

6、終極的三表聯結查詢

items:商品表,item_visit_stats:商品訪問表,item_trade_stats:商品銷售表

SELECT i.num_iid, i.title, i.price, SUM(iv.user_visits) AS uv,it.buyer_num,it.item_num,it.item_num*i.price AS turnover
FROM (items AS i RIGHT JOIN item_visit_stats AS iv ON i.num_iid=iv.num_iid)
LEFT JOIN (SELECT num_iid,SUM(buyer_num) AS buyer_num,SUM(item_num) AS item_num FROM item_trade_stats
WHERE seller_nick="XXXX" AND business_day BETWEEN '2010-08-14' AND '2010-08-15' GROUP BY num_iid)
AS it ON it.num_iid=iv.num_iid 
WHERE i.nick="XXXX" AND iv.business_day BETWEEN '2010-08-14' AND '2010-08-15'
GROUP BY i.num_iid ORDER BY uv DESC

相關文章