表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
代码如下 复制代码
select * from a left join b on a.aid = b.bid
首先取出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总和最近的一个表挨着，同时需要用借助于括号