SQL语句：感觉很简单的问题，可是我不会~请帮忙

[半老徐男]

表1: a

---------------
a1 a2 a3
---------------
1 10 0.5
2 20 0
3 5 5


表2: b

---------------
a1 b2 b3
---------------
1 1.5 0.5
1 5.5 0.5
3 6 5

其中a.a1=b.a1

麻烦帮忙写个SQL语句， 怎么样才能查出a2的和减去b2的和，然后按a1分组，显示表a里所有的a1信息，
现在的麻烦就在有的a.a1在表b中没有值。

即得出如下结果：
---------------------
a1 sum(a2)-sum(b2)
---------------------
1 3
2 20
3 -1

[bqb]

select a.a1,a.a2-b.b2 from a left join (select a1,sum(b2) from b group by a1) b on a.a1=b.a1

[viva369]

create table #a
(
	a1 float,
	a2 float,
	a3 float 
)

create table #b
(
	b1 float,
	b2 float,
	b3 float 
)

insert #a select 1  ,10  ,0.5 
insert #a select 2  ,20  ,0 
insert #a select 3  ,5   ,5

insert #b select 1 , 1.5 , 0.5
insert #b select 1 , 5.5 , 0.5
insert #b select 3,6,5
go
select #a.a1,sum(#a.a2)-isnull((select sum(b2) from #b where b1=#a.a1 group by b1),0) from #a
group by #a.a1

[viva369]

group by b1--多余了~

[pt1314917]

declare @a table(a1 int,a2 money,a3 money)
insert into @a select 1,10,0.5
insert into @a select 2,20,0
insert into @a select 3,5,5

declare @b table(a1 int,b2 money,b3 money)
insert into @b select 1,1.5,0.5
insert into @b select 1,5.5,0.5
insert into @b select 3,6,5


select a.a1,a2-isnull(b2,0)
from @a a left join 
(select a1,sum(b2)b2 from @b group by a1) b
on a.a1=b.a1

[半老徐男]

To viva369

结果非常正确，很感谢~

[net3]

SELECT A.A1,A.A2-ISNULL(B.B2,0) FROM AT A
LEFT JOIN (SELECT A1 ,SUM(B2) B2 FROM BT GROUP BY A1) B
ON A.A1=B.A1

[Limpire]

[Quote=引用 5 楼 xhj0592 的回复:]
To viva369

结果非常正确，很感谢~
[/Quote]

那还不结贴？