求教一个sql问题

[啊雪莉]

表A
id number
A 5
A 3
B 5
B 3
C 5
D 5
E 5
-------------------------------------
表B
id number
B 3
B 1
C 5
D 5
--------------------------------------
我希望能获取表A和表B之间sum(number)的差，如果表B中不存在，则显示表A的值

id number
A 8
B 4
C 0
D 0
E 5

请问下该如何实现

[唐诗三百首]

select a.id, 
       number=a.number-isnull(b.number,0)
 from (select id,number=sum(number)
        from 表A
        group by id) a
 left join (select id,number=sum(number)
             from 表B
             group by id) b on a.id=b.id

[啊雪莉]

引用 1 楼 唐诗三百首 的回复:

select a.id, 
       number=a.number-isnull(b.number,0)
 from (select id,number=sum(number)
        from 表A
        group by id) a
 left join (select id,number=sum(number)
             from 表B
             group by id) b on a.id=b.id

好了，多谢