有两表 A 和 B,
A表结构和数据如下:
GoodID Num Date
1001 100 2006-01-01
1001 200 2006-02-03
1002 300 2006-02-02
1003 100 2006-04-08表B结构和数据如下:
GoodID Num Date
1001 50 2006-01-01
1001 40 2006-02-03
1002 30 2006-02-02现在我想得到如下结果:(结果Num是这样得到的:将A表的Num按GoodID汇总后 - 将B表的Num按GoodID汇总后)
GoodID NumAfter Date
1001 210 2006-02-03
1002 270 2006-02-02
1003 100 2006-04-08
谢谢啊!!!
A表结构和数据如下:
GoodID Num Date
1001 100 2006-01-01
1001 200 2006-02-03
1002 300 2006-02-02
1003 100 2006-04-08表B结构和数据如下:
GoodID Num Date
1001 50 2006-01-01
1001 40 2006-02-03
1002 30 2006-02-02现在我想得到如下结果:(结果Num是这样得到的:将A表的Num按GoodID汇总后 - 将B表的Num按GoodID汇总后)
GoodID NumAfter Date
1001 210 2006-02-03
1002 270 2006-02-02
1003 100 2006-04-08
谢谢啊!!!
from (select GoodId, num, Date
from tableA
union
select GoodId, -num, Date
from tableB) a
group by GoodId
select 1001 as GoodId, 100 as Num, '2006-01-01' as Date
into tableA
union select 1001, 200, '2006-02-03'
union select 1002, 300, '2006-02-02'
union select 1003, 100, '2006-04-08'if object_id('tableB') is not null drop table tableB
select 1001 as GoodId, 50 as Num, '2006-01-01' as Date
into tableB
union select 1001, 40, '2006-02-03'
union select 1002, 30, '2006-02-02'
---------------------------------------
select GoodId, sum(Num) as NumAfter, max(Date) as Date
from (select GoodId, num, Date
from tableA
union
select GoodId, -num, Date
from tableB) a
group by GoodId
/*
GoodId NumAfter Date
1001 210 2006-02-03
1002 270 2006-02-02
1003 100 2006-04-08
*/
---------------------------------------
drop table tableA
drop table tableB
GoodID NumAfter
1001 210
1002 270
1003 100 有没有常规点的方法,欢迎也提出来。 我在此表示万分感谢!!!
select a.GoodId, sum(Num) as NumAfter
from (select GoodId, num
from tableA
union
select GoodId, -num
from tableB) a
group by GoodId