34,838
社区成员




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
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
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