A表whi iflag NowNum
z1 max001 100
z1 max002 60
z2 max001 50
z3 max002 150
B表 whu asiflag whunownum
z1 max001 60
z1 max003 210
z2 max001 80条件A.whi=z1 B.whu=z1我需要得出C表
iflag NowNum asiflag whunownum cy
max001 100 max001 60 40
null null max003 210 -210
max002 60 null null 60
z1 max001 100
z1 max002 60
z2 max001 50
z3 max002 150
B表 whu asiflag whunownum
z1 max001 60
z1 max003 210
z2 max001 80条件A.whi=z1 B.whu=z1我需要得出C表
iflag NowNum asiflag whunownum cy
max001 100 max001 60 40
null null max003 210 -210
max002 60 null null 60
(case when a.nownum is null then 0 else a.nownum end ) -
(case when b.whunownum is null then 0 else b.whunownum end ) as cy
from a full outer join b on a.whi = b.whi and a.iflag = b.asiflag
where a.whi = 'z1'
select *,(A.NowNum - B.whunownum) as yu from A
full join B on A.whi = B.whi and A.iflag = B.asiflag
where A.whi = 'z1'
A表里面 有2条这样的数据 需要求和z1 max001 100
z1 max001 50就是A表有重复数据这个解决了马上给分解贴。
from
(select * from A where A.whi=z1) tempA
full jion
(select * from B where B.whu=z1) tempB
on tempA.iflag=tempB.asiflag
declare @ta table (whi varchar(10),iflag varchar(10),NowNum int)
declare @tb table (whi varchar(10),sdiflag varchar(10),whunowNum int)insert @ta
select 'z1','max001',100 union all
select 'z1','max002',60 union all
select 'z2','max001',50 union all
select 'z3','max002',150 insert @tb
select 'z1','max001',60 union all
select 'z1','max003',210 union all
select 'z2','max001',80 select * from (
select a.whi,a.iflag,a.NowNum,b.sdiflag,b.whunowNum, case when a.NowNum is null then 0 else a.NowNum end - case when b.whunowNum is null then 0 else b.whunowNum end cy from @ta a left join @tb b on a.whi = b.whi and a.iflag = b.sdiflag
union all
select b.whi,a.iflag,a.NowNum,b.sdiflag,b.whunowNum,case when a.NowNum is null then 0 else a.NowNum end - case when b.whunowNum is null then 0 else b.whunowNum end cy from @tb b left join @ta a on a.whi = b.whi and a.iflag = b.sdiflag
) c group by iflag,NowNum,sdiflag,whunowNum,cy,whi----结果whi iflag NowNum sdiflag whunowNum cy
---------- ---------- ----------- ---------- ----------- -----------
z1 NULL NULL max003 210 -210
z2 max001 50 max001 80 -30
z1 max001 100 max001 60 40
z1 max002 60 NULL NULL 60
z3 max002 150 NULL NULL 150
if object_id('ta') is not null
drop table ta
go
create table ta(whi varchar(2),iflag varchar(10),nownum int)
insert into ta
select 'z1', 'max001', 100 union all
select 'z1', 'max002', 60 union all
select 'z2', 'max001', 50 union all
select 'z3', 'max002', 150
if object_id('tb') is not null
drop table tb
go
create table tb(whu varchar(2),asiflag varchar(10),whunownum int)
insert into tb
select 'z1', 'max001', 60 union all
select 'z1', 'max003', 210 union all
select 'z2', 'max001', 80
select iflag,nownum,asiflag,whunownum,cy=isnull(a.nownum,0)-isnull(b.whunownum,0)
from(select * from ta
where whi='z1') a left join (select * from tb
where whu='z1') b on a.iflag=b.asiflag
union
select iflag,nownum,asiflag,whunownum,cy=isnull(a.nownum,0)-isnull(b.whunownum,0)
from(select * from ta
where whi='z1') a right join (select * from tb
where whu='z1') b on a.iflag=b.asiflagNULL NULL max003 210 -210
max001 100 max001 60 40
max002 60 NULL NULL 60
select k.iflag,k.nownum,b.asiflag,b.whunownum,
((case when k.nownum is null then 0 else k.nownum end ) -
(case when b.whunownum is null then 0 else b.whunownum end )) as cy
from ( select whi,iflag,sum(nownum) as nownum from a group by whi,iflag ) k
full outer join b on k.whi = b.whi and k.iflag = b.asiflag
where k.whi = 'z1'
declare @ta table (whi varchar(10),iflag varchar(10),NowNum int)
declare @tb table (whi varchar(10),sdiflag varchar(10),whunowNum int)insert @ta
select 'z1','max001',100 union all
select 'z1','max001',50 union all ---新增 z1 max001 50
select 'z1','max002',60 union all
select 'z2','max001',50 union all
select 'z3','max002',150 insert @tb
select 'z1','max001',60 union all
select 'z1','max003',210 union all
select 'z2','max001',80 select whi,iflag,sum(NowNum),sdiflag,whunowNum,sum(cy) from (
select case when a.whi is null then b.whi else a.whi end whi,a.iflag,a.NowNum,b.sdiflag,b.whunowNum, case when a.NowNum is null then 0 else a.NowNum end - case when b.whunowNum is null then 0 else b.whunowNum end cy
from @ta a full join @tb b on a.whi = b.whi and a.iflag = b.sdiflag
) c group by iflag,sdiflag,whunowNum,whi
-----
whi iflag sdiflag whunowNum
---------- ---------- ----------- ---------- ----------- -----------
z1 NULL NULL max003 210 -210
z1 max001 150 max001 60 30
z2 max001 50 max001 80 -30
z1 max002 60 NULL NULL 60
z3 max002 150 NULL NULL 150(所影响的行数为 5 行)
-----
whi iflag sdiflag whunowNum
---------- ---------- ----------- ---------- ----------- -----------
z1 NULL NULL max003 210 -210
z1 max001 150 max001 60 30 ---max001 150
z2 max001 50 max001 80 -30
z1 max002 60 NULL NULL 60
z3 max002 150 NULL NULL 150
from
(select A.whi, A.iflag,sum(A.NowNum) Anum from A where A.whi=z1 group by A.whi,A.iflag) tempA
full jion
(select * from B where B.whu=z1) tempB
on tempA.iflag=tempB.asiflag