select a.a,sum(b.e) as e
from a
,b
where ',' + a.b + ',' like '%,' + b.c + ',%'
group by a.a

------解决方案--------------------
SQL code
;with tt as 
(
select a.id , value = substring(a.value , b.number , charindex(',' , a.value + ',' , b.number) - b.number) 
from 表1 a join master..spt_values  b 
on b.type='p' and b.number between 1 and len(a.value)
where substring(',' + a.value , b.number , 1) = ','
)
select tt.id,sum(表2.D) from tt ,表2 where tt.value = 表2.C

------解决方案--------------------
SQL code


select t1.A,SUM(ISNULL(t2.D,0)) as SumD from 表1 t1
left join 表2 t2 on Charindex(','+t2.C+',',','+t1.B+',')>0
group by t1.A