表A
a b c d
1 1 1 1
2 2 2 2
表B
a b c d
1 1 1 1
用select A.a,(A.b-B.b),(A.c-B.c),(A.d-B.d) from A FULL JOIN B on A.a = B.a得出的结果是
a b c d
1 0 0 0
2 null null null想要的结果是
表C(下面是想要的结果)
a b c d
1 0 0 0
2 2 2 2
a b c d
1 1 1 1
2 2 2 2
表B
a b c d
1 1 1 1
用select A.a,(A.b-B.b),(A.c-B.c),(A.d-B.d) from A FULL JOIN B on A.a = B.a得出的结果是
a b c d
1 0 0 0
2 null null null想要的结果是
表C(下面是想要的结果)
a b c d
1 0 0 0
2 2 2 2
if object_id('aT1') is not null drop table aT1
create table aT1 (a int,b int,c int,d int)
insert into aT1
select 1,1,1,1 union all
select 2,2,2,2
--> 测试数据: #b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b (a int,b int,c int,d int)
insert into #b
select 1,1,1,1select t1.a,
t1.b-isnull(t2.b,0) b,
t1.c-isnull(t2.c,0) c,
t1.d-isnull(t2.d,0) d from aT1 t1
left join #b t2 on t1.a=t2.a
a b c d
----------- ----------- ----------- -----------
1 0 0 0
2 2 2 2(2 行受影响)
如下为Oracle中的NVL(aa,0)
select A.a,(A.b-NVL(B.b,0)),(A.c-NVL(B.c,0)),(A.d-NVL(B.d,0)) from A FULL JOIN B on A.a = B.a