select * from schedulingShipSchedule where Sub_ShipDate1<>''and Sub_Qty1<>'' and TeamHandlingBy='Non-Mailing'
以上代碼可以得到55條記錄
with a as
(select * from schedulingShipSchedule where Sub_ShipDate1<>''and Sub_Qty1<>'' and TeamHandlingBy='Non-Mailing'),
b as
(select * from DirectOrderV2soitems where invoiceornot='2' or invoiceornot='3')
select a.JobNumber,a.Version,b.HonourJob,b.itemnumber
from a left join b on
a.Version=b.itemnumber and a.JobNumber=b.HonourJob
為什麼使用左連接之後出現63條記錄，為什麼不是55條記錄？
------解决方案--------------------
举例说明什么是一对多..
with a as
( select 1 'id', 'a1' 'x' union all
select 2 'id', 'a2' 'x' union all
select 3 'id', 'a3' 'x'
),
b as
( select 1 'id', 'b1' 'y' union all
select 2 'id', 'b21' 'y' union all
select 2 'id', 'b22' 'y' union all
select 2 'id', 'b23' 'y' union all
select 3 'id', 'b3' 'y'
)
select *
from a
left join b on a.id=b.id
-- 结果, a表数据3条,左连接之后出现5条记录.因为a.id=2的记录对应b.id=2有3条记录.
/*
id x id y
----------- ---- ----------- ----
1 a1 1 b1
2 a2 2 b21
2 a2 2 b22