跪求解答方法，2表差异

[fuzongfan1]

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

[fuzongfan1]

就是求2表差异生成一个表 包含原来2个表的数据和一个差异列

是有点难度，所以满分求方法。

[打一壶酱油]

select a.iflag,a.nownum,b.asiflag,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  ) as cy
from a full outer join b on a.whi = b.whi and a.iflag = b.asiflag 
where a.whi = 'z1' 

[symbol_bc]

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' 

[fuzongfan1]

谢谢上面2位大大，还有个小小的问题
A表里面 有2条这样的数据 需要求和

z1 max001 100
z1 max001 50

就是A表有重复数据这个解决了马上给分解贴。

[gudujianxiao]

select tempA.iflag,tempA.NowNum,tempB.asiflag,tempB.whunownum,isnull(tempA.nownum,0)-isnull(tempB.whunownum) cy
from
(select * from A where A.whi=z1) tempA
full jion
(select * from B where B.whu=z1) tempB
on tempA.iflag=tempB.asiflag

[Mark杨]

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

[jaydom]

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.asiflag

NULL	NULL	max003	210	-210
max001	100	max001	60	40
max002	60	NULL	NULL	60

[打一壶酱油]

[Quote=引用 4 楼 fuzongfan1 的回复:]
谢谢上面2位大大，还有个小小的问题
A表里面 有2条这样的数据 需要求和

z1 max001 100
z1 max001 50

就是A表有重复数据这个解决了马上给分解贴。
[/Quote]

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' 

[Mark杨]

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 行）

[fuzongfan1]

谢谢各位精彩的解答，abcjun188 2次解答算到一起，希望你同意。其他大大也辛苦了，4：6的比例希望你们满意。

[Mark杨]

-----
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

[gudujianxiao]

select tempA.iflag,tempA.NowNum,tempB.asiflag,tempB.whunownum,tempA.Anum-isnull(tempB.whunownum) cy
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