这种结果能不能实现,请

这种结果能不能实现,请高手指点
表A:
rsid     zd1       sl
1           a           5
1           b           7
2           a           6
2           b           4

表B
rsid     zd2       sl
1             a         7
1             a         4
2             a         6
2             a         9

我现在想得到:不同的rsid在表A和表B中zd1为a和zd2为a时的sl的和是多少,也就是说我想得到如下结果:1     a     2,11
                                  2     a     6,15
不知道一条语句能不能实现


[解决办法]
select a.rsid,a.zd1,cast(a.sl as varchar)+ ', '+cast(b.sl as varchar) as sl
from (
select rsid,zd1,sum(sl) as sl from 表A where zd1 = 'a ' group by rsid,zd1
) as a,(
select rsid,zd2,sum(sl) as sl from 表B where zd1 = 'a ' group by rsid,zd2
) as b
where a.rsid=b.rsid


[解决办法]
create table t1( rsid int,zd1 varchar(10),sl int)
create table t2( rsid int,zd2 varchar(10),sl int)

insert t1 select 1, 'a ',5
union all select 1, 'b ',7
union all select 2, 'a ',6
union all select 2, 'b ',4

insert t2 select 1, 'a ',7
union all select 1, 'a ',4
union all select 2, 'a ',6
union all select 2, 'a ',9

select distinct t1.rsid,zd1,t1.sl,total from t1 right join
(
select rsid,zd2,total=sum(sl) from t2 group by rsid,zd2
)a
on t1.rsid=a.rsid and t1.zd1=a.zd2

/*
rsid zd1 sl total
----------- ---------- ----------- -----------
1 a 5 11
2 a 6 15
*/