现在有三张表 数据如
tab1
id shuliang
001 15
001 15
002 22
002 10
003 10
003 2
003 2
006 2
007 20tab2
id shuliang
001 12
001 12
002 12
002 13
004 45
005 20tab3
id shuliang
001 12
002 20
002 13
004 16
008 14
我现在想要得到的结果是
tab1表中的数据加上tab2 中的数据 减去 tab3 中的数据谢谢
tab1
id shuliang
001 15
001 15
002 22
002 10
003 10
003 2
003 2
006 2
007 20tab2
id shuliang
001 12
001 12
002 12
002 13
004 45
005 20tab3
id shuliang
001 12
002 20
002 13
004 16
008 14
我现在想要得到的结果是
tab1表中的数据加上tab2 中的数据 减去 tab3 中的数据谢谢
id shuliang
001 42
002 22
003 14
004 29
005 20
006 2
007 20
008 -14谢谢
select
id,sum(shuliang) as shuliang
from
(select * from tab1
union all
select * from tab2
union all
select id,-shuliang from tab3) t
group by
id
having
sum(shuliang)=0
insert into tab1 select '001', 15
union all select '001', 15
union all select '002', 22
union all select '002', 10
union all select '003', 10
union all select '003', 2
union all select '003', 2
union all select '006', 2
union all select '007', 20 create table tab2(id varchar(10), shuliang int)
insert into tab2 select '001', 12
union all select '001', 12
union all select '002', 12
union all select '002', 13
union all select '004', 45
union all select '005', 20create table tab3 (id varchar(10), shuliang int)
insert into tab3 select '001', 12
union all select '002', 20
union all select '002', 13
union all select '004', 16
union all select '008', 14select isnull(a.id,b.id)[id],isnull(a.shuliang,0)-isnull(b.shuliang,0)[shuliang] from
(select id,sum(shuliang)[shuliang]
from(select * from tab1
union all select * from tab2)a
group by id)a
full join
tab3 b on b.id=a.id
from(select id,sum(shuliang)[shuliang]
from(select * from tab1
union all select * from tab2)a
group by id)a
full join
(select id, sum(shuliang)[shuliang] from tab3 group by id) b on b.id=a.id
id shuliang
---------- -----------
001 42
002 24
003 14
004 29
005 20
006 2
007 20
008 -14
from
(
select id , shuliang from tab1
union all
select id, shuliang from tab2
union all
select id,-shuliang as shuliang from tab3
) T
group by id
select id,sum(shuliang)
from
(
select id , shuliang from tab1
union all
select id, shuliang from tab2
union all
select id,-shuliang as shuliang from tab3
) T
group by id
from(select id,sum(shuliang)[shuliang] from tab1 group by id)a
full join
(select id,sum(shuliang)[shuliang] from tab2 group by id)b on b.id=a.id
full join
(select id,sum(shuliang)[shuliang] from tab3 group by id)c on c.id=b.id