表如下
表1
A B
1 10,11,12,13
2 14,15
...
表2
C D
10 100
11 200
14 300
15 400
...
得到如下 意思是查询A=1或者2时 表2中C为表1 B中的数值时,合计D出来.
表3
A E
1 300
2 700
...
------解决方案--------------------
- SQL code
select a.a,sum(b.e) as efrom a,bwhere ',' + 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 t1left join 表2 t2 on Charindex(','+t2.C+',',','+t1.B+',')>0group by t1.A