select isnull(a.ITEM_CODE,isnull(b.ITEM_CODE,c.ITEM_CODE)) as ITEM_CODE, isnull(a.OutNumber,0) as OutNumber, isnull(b.InNumber,0) as InNumber, isnull(c.ChuNumber,0) as ChuNumber from ta a full join tb b on a.ITEM_CODE = b.ITEM_CODE full join tc c on a.ITEM_CODE = c.ITEM_CODE or b.ITEM_CODE = c.ITEM_CODE
create table A(ITEM_CODE int, OutNumber int) insert a SELECT 1 ,100 insert a SELECT 2 ,500 insert a SELECT 5 ,200create table B(ITEM_CODE int, InNumber int) insert B SELECT 1 ,400 insert B SELECT 2 ,300 insert B SELECT 3 ,100create table C(ITEM_CODE int, ChuNumber int) insert C SELECT 1 ,100 insert C SELECT 2 ,300 insert C SELECT 6 ,100 SELECT ISNULL(ISNULL(A.ITEM_CODE,B.ITEM_CODE),C.ITEM_CODE) AS ITEM_CODE, ISNULL(OutNumber,0) AS OutNumber, ISNULL(InNumber,0) AS InNumber, ISNULL(ChuNumber,0) AS ChuNumber FROM a FULL JOIN b ON A.ITEM_CODE=B.ITEM_CODE FULL JOIN C ON A.ITEM_CODE=C.ITEM_CODE ORDER BY 1ITEM_CODE OutNumber InNumber ChuNumber ----------- ----------- ----------- ----------- 1 100 400 100 2 500 300 300 3 0 100 0 5 200 0 0 6 0 0 100(5 行受影响)
select COALESCE(a.ITEM_CODE,b.ITEM_CODE,c.ITEM_CODE) ITEM_CODE, isnull(a.OutNumber,0) OutNumber, isnull(b.InNumber,0) InNumber, isnull(c.ChuNumber,0) ChuNumber from a full join b on a.ITEM_CODE=b.ITEM_CODE full join c on a.ITEM_CODE=c.ITEM_CODE or b.ITEM_CODE=c.ITEM_CODE 试试
full jion 如果同一个ITEM_CODE有多条 先聚合 然后再连接
--环境 create table ta (ITEM_CODE int, OutNumber int) insert into ta select 1, 100 insert into ta select 2, 500 insert into ta select 5, 200create table tb (ITEM_CODE int, InNumber int) insert into tb select 1, 400 insert into tb select 2, 300 insert into tb select 3, 100create table tc (ITEM_CODE int, ChuNumber int) insert into tc select 1, 100 insert into tc select 2, 300 insert into tc select 6, 100--查询 select isnull(a.ITEM_CODE,isnull(b.ITEM_CODE,c.ITEM_CODE)) as ITEM_CODE, isnull(a.OutNumber,0) as OutNumber, isnull(b.InNumber,0) as InNumber, isnull(c.ChuNumber,0) as ChuNumber from ta a full join tb b on a.ITEM_CODE = b.ITEM_CODE full join tc c on a.ITEM_CODE = c.ITEM_CODE or b.ITEM_CODE = c.ITEM_CODE--结果 /* 1 100 400 100 2 500 300 300 5 200 0 0 3 0 100 0 6 0 0 100 */
select isnull(a.ITEM_CODE,isnull(b.ITEM_CODE,c.ITEM_CODE)) as ITEM_CODE,
isnull(a.OutNumber,0) as OutNumber,
isnull(b.InNumber,0) as InNumber,
isnull(c.ChuNumber,0) as ChuNumber
from ta a full join tb b on a.ITEM_CODE = b.ITEM_CODE
full join tc c on a.ITEM_CODE = c.ITEM_CODE or b.ITEM_CODE = c.ITEM_CODE
insert a SELECT 1 ,100
insert a SELECT 2 ,500
insert a SELECT 5 ,200create table B(ITEM_CODE int, InNumber int)
insert B SELECT 1 ,400
insert B SELECT 2 ,300
insert B SELECT 3 ,100create table C(ITEM_CODE int, ChuNumber int)
insert C SELECT 1 ,100
insert C SELECT 2 ,300
insert C SELECT 6 ,100
SELECT ISNULL(ISNULL(A.ITEM_CODE,B.ITEM_CODE),C.ITEM_CODE) AS ITEM_CODE,
ISNULL(OutNumber,0) AS OutNumber,
ISNULL(InNumber,0) AS InNumber,
ISNULL(ChuNumber,0) AS ChuNumber
FROM a
FULL JOIN b ON A.ITEM_CODE=B.ITEM_CODE
FULL JOIN C ON A.ITEM_CODE=C.ITEM_CODE
ORDER BY 1ITEM_CODE OutNumber InNumber ChuNumber
----------- ----------- ----------- -----------
1 100 400 100
2 500 300 300
3 0 100 0
5 200 0 0
6 0 0 100(5 行受影响)
select COALESCE(a.ITEM_CODE,b.ITEM_CODE,c.ITEM_CODE) ITEM_CODE,
isnull(a.OutNumber,0) OutNumber,
isnull(b.InNumber,0) InNumber,
isnull(c.ChuNumber,0) ChuNumber
from a full join b on a.ITEM_CODE=b.ITEM_CODE
full join c on a.ITEM_CODE=c.ITEM_CODE or b.ITEM_CODE=c.ITEM_CODE
试试
如果同一个ITEM_CODE有多条
先聚合
然后再连接
--环境
create table ta
(ITEM_CODE int,
OutNumber int)
insert into ta select 1, 100
insert into ta select 2, 500
insert into ta select 5, 200create table tb
(ITEM_CODE int,
InNumber int)
insert into tb select 1, 400
insert into tb select 2, 300
insert into tb select 3, 100create table tc
(ITEM_CODE int,
ChuNumber int)
insert into tc select 1, 100
insert into tc select 2, 300
insert into tc select 6, 100--查询
select isnull(a.ITEM_CODE,isnull(b.ITEM_CODE,c.ITEM_CODE)) as ITEM_CODE,
isnull(a.OutNumber,0) as OutNumber,
isnull(b.InNumber,0) as InNumber,
isnull(c.ChuNumber,0) as ChuNumber
from ta a full join tb b on a.ITEM_CODE = b.ITEM_CODE
full join tc c on a.ITEM_CODE = c.ITEM_CODE or b.ITEM_CODE = c.ITEM_CODE--结果
/*
1 100 400 100
2 500 300 300
5 200 0 0
3 0 100 0
6 0 0 100
*/