问一sql语句两表某列数据相减,该怎么解决

问一sql语句两表某列数据相减
比如表1
ID count
1 2
2 3
4 1
5 4

表2
ID count
2 2
3 3
5 6
7 1
我要的结果是表1减去表2的结果
结果如下
ID count
1 2
2 1
3 -3
4 1
5 -2
7 -1

[解决办法]

SQL code

--> 测试数据：[表1]if object_id('[表1]') is not null drop table [表1]create table [表1]([ID] int,[count] int)insert [表1]select 1,2 union allselect 2,3 union allselect 4,1 union allselect 5,4--> 测试数据：[表2]if object_id('[表2]') is not null drop table [表2]create table [表2]([ID] int,[count] int)insert [表2]select 2,2 union allselect 3,3 union allselect 5,6 union allselect 7,1select id,sum([count]) as [count] from(select * from [表1]union all select id,-[count] from [表2])agroup by id order by id asc/*id    count1    22    13    -34    15    -27    -1*/
[解决办法]
SQL code
declare @t1 table(id int,count int)insert into @t1select 1,2 union allselect 2,3 union allselect 4,1 union allselect 5,4 declare @t2 table(id int,count int)insert into @t2select 2,2 union allselect 3,3 union allselect 5,6 union allselect 7,1select * from (select ID,COUNT from @t1 where id not in(select id from @t2)union allselect ID,0-COUNT from @t2 where id not in(select id from @t1)union allselect a.id,a.count-b.count from @t1 a join @t2 b on a.id=b.id) as a order by id
[解决办法]
SELECT id,SUM(COUNT) FROM (
SELECT id,COUNT count FROM t1
UNION ALL
SELECT id,-COUNT count FROM t2
) b  
GROUP BY id