求一条SQL语句。两个表中的数据分别求和 ( 积分: 200 )

表：A(入库) 表：B（出库） 由A和B表得出 表 C
--------- --------------- ----------------------
ID count ID count ID Rcount Ccount R-C
01 10 01 1 01 12 6 6
02 5 01 2 02 5 3 2
03 8 01 3 03 9 6 3
01 2 02 2
03 1 02 1
03 2
03 4
用SQL 2000 的语句该怎么写呢？ 谢谢大家了。

select b.id,rcount=sum(isnull(a.count,0)),ccount=sum(isnull(b.count,0)),
rc=sum(isnull(a.count,0)-isnull(b.count,0)) from a right join b on a.id=b.id

to Yhhe:
你的语句后面应该是短了 group by b.id 吧
按这条语句本出来的结果是
01 36 12 24
02 10 3 7
03 18 12 6
结果不正确呀.

错误的,下面是分别分组统计,再合并
select * from
(select id,sum([count]) as qty from atb group by id) as a,
(select id,sum([count]) as qty from btb group by id) as b
where a.id = b.id
注意count 是关键字(函数)

楼上差不多了的，就是少个相减的函数

select id,qty1,qty2,qty1-qty2 as rc from
(select id,sum([count]) as qty1 from atb group by id) as a,
(select id,sum([count]) as qty2 from btb group by id) as b
where a.id = b.id
改樓上的代碼，