Question

求助：SQL分类汇总求和运算

A表 ID 编号 数量
01 0101 3
01 0102 3
02 0201 1
02 0202 2
03 0301 3

B表
ID 编号 数量
01 0101 4
02 0201 2
03 0301 3

select sum(A.数量) as 数量A from A group by ID
select sum(B.数量) as 数量B from B group by ID
然后：数量A-数量B

求结果为：

C表 ID 数量
01 2
02 1
03 0

问SQL里怎么实现啊，怎么写SQL语句啊？谢谢了

Answer 1

select t1.id, t1.数量 - isnull(t2.数量, 0) as 数量
from
(select id, sum(数量) as 数量
from a
group by id ) t1 left join
(select id, sum(数量) as 数量
from b
group by id) t2 on t1.id = t2.id

使用left join是担心a表的id会在b表不存在，那么就会缺少这个id的差值了。

Answer 2

SELECT A.ID, SUM(A.数量- B.数量)
FROM A, B
WHERE A.ID = B.ID
GROUP BY A.ID