表a
1 aid adate
2 1 a1
3 2 a2
4 3 a3
表b
1 bid bdate
2 1 b1
3 2 b2
4 4 b4
1
首先取出a表中所有數據,然後再加上與a、b匹配的的數據。
此時的取出的是:
1 1 a1 b1
2 2 a2 b2
3 3 a3 空字符
同樣的也有right join
指的是首先取出b表中所有數據,然後再加上與a、b匹配的的數據。
此時的取出的是:
1 1 a1 b1
2 2 a2 b2
left join性能
1例子
select distinct count('goods_id') as num , g.goods_id,g.goods_name from ms_goods as g left join ms_order_goods as og on g.goods_id = og.goods_id group by goods_id
顯示行 0 - 29 (1,475 總計, 查詢花費 0.0167 秒)
2例子
代碼如下 復制代碼select distinct count('goods_id') as num , g.goods_id,g.goods_name from ms_goods as g , ms_order_goods as og where g.goods_id = og.goods_id group by goods_id
顯示行 0 - 29 (257 總計, 查詢花費 0.0088 秒)
實例
表結構如下:
MDate StoreCode GoodsCode ToStoreCode GoodsAmount GoodsFlag
2005-12-12 001 101 888 2 3
2005-12-13 001 101 3 1
2005-12-15 001 101 1 2
三條記錄的意思分別為:
2005-12-12從001移到888 2件
2005-12-13從001銷售 3件
2005-12-15倉店001進貨 2件
出報表
StoreCode GoodsCode StockAmount InAmount OutAmount SellAmount
001 101 3 0 2 1
注:StockAmount進貨數量 InAmount移入數量 OutAmount移出數量 SellAmount銷售數量
select
StoreCode,
GoodsCode,
StockAmount = sum(case GoodsFlag when 2 then GoodsAmount else 0 end),
InAmount = sum(case GoodsFlag when 4 then GoodsAmount else 0 end),
OutAmount = sum(case GoodsFlag when 3 then GoodsAmount else 0 end),
SellAmount = sum(case GoodsFlag when 1 then GoodsAmount else 0 end)
from
表
group by
StoreCode,GoodsCode
例子3
po_order_det 表
ID MA_ID QTY
01 #21鋼 30
02 #22鋼 40
03 #23鋁 30
ST_CONVER 表
ID QTY
01 20
02 10
要求返回集 QTY = po_order_det.QTY - ST_CONVER.QTY AND po_order_det.ID=ST_CONVER.ID
ID MA_ID QTY
01 #21鋼 10
02 #22鋼 30
03 #23鋁 30
select a.order_id,a.id,a.ma_id,a.qty,isnull(b.qty,0) qtyy, isnull(a.qty - b.qty ,0) qtyx
from po_order_det a left join st_conver b
on a.id=b.id and a.filid=b.filid
and a.id=b.id and a.order_id=b.order_id
Where a.filid='S'
總結
SQL 中只出現一個左連接:
代碼如下 復制代碼select a.*, b.*, c.* from b, a
left join c on a.id = c.id
where b.id = a.iid
注意 表a 要和最近的一個關聯的leftjoin挨著。
SQL中出現了2個或者多個左連接:
代碼如下 復制代碼
select a.*, b.* c.*, d.*, e.*
from ((
c, b, a
)
left join d on d.id = a.id
) left join e on e.id = b.id
where c.id = b.id
and b.id = a.id
leftjoin總和最近的一個表挨著,同時需要用借助於括號