求一条求和的SQL语句，在线等，谢谢

来源：百度知道 编辑：UC知道 时间：2024/05/24 20:06:43
表A：a1 a2
ss pp
mm kk
表B：b1 b2
ss 12
ss 14
ss 20
mm 8
mm 10
我要得到的结果为：
C：c1 c2 c3
ss pp 46
mm kk 18

c3就是把B表中为同一组的加起来，哪位高手会啊？

select A.a1 as c1,A.a2 as c2,test.b2 as c3
from A inner join
(select b1,sum(b2) as b2 from B group by b1) test
on A.a1=test.b1;
绝对可以

--> 测试数据: @A
declare @A table (a1 varchar(2),a2 varchar(2))
insert into @A
select 'ss','pp' union all
select 'mm','kk'
--> 测试数据: @B
declare @B table (b1 varchar(2),b2 int)
insert into @B
select 'ss',12 union all
select 'ss',14 union all
select 'ss',20 union all
select 'mm',8 union all
select 'mm',10

select c1 = a.a1
,c2 = a.a2
,c3 = (select sum(b2) from @b where b1 = a.a1)
from @A a

c1 c2 c3
---- ---- -----------
ss pp 46
mm kk 18

（所影响的行数为 2 行）

相关子查询
------------------
SELECT a1 AS C1,a2 AS C2,
ISNULL( (SELECT SUM(b2) FROM 表B WHERE 表B.b1=表A.a1) ,0) AS C3
FROM 表A
希望对你有帮