Question

oracle SQL 查询某一字段中的字符串出现的次数

如表T ：
T1 T2
1 A,B,C
2 A,B
3 B,C
查询出的结果如下P：
P1 P2
A 2
B 3
C 2
其实就是查A,B,C在表T1中出现的次数。T表中T2的“A,B,C”可以有C,D,......
系统已经上线，客户要新增一报表，希望有人能帮下，在此谢谢！！！

Answer 1

select 'A' P1,a.P2
from (select count(1) P2 from t where t.t2 like '%A%' ) a
union all
select 'B',b.P2
from (select count(1) P2 from t where t.t2 like '%A%' ) b
.....

Answer 2

select id=identity(int,1,1)
into #a from dbo.syscolumns a, dbo.syscolumns b

select substring(a.t2,b.id,charindex(',',a.t2+',',b.id)-b.id),count(a.t1)

from tablet a,#a b
where b.id<=len(a.t2)
and substring(','+a.t2,b.id,1)=','
group by substring(a.t2,b.id,charindex(',',a.t2+',',b.id)-b.id)

Answer 3

select B.A,count(*) total from A,B where A.A like '%'+B.A+'%' group by B.A
下面是赠送的：
select chr(ascii('A') + level - 1) from dual connect by rownum <= 26
如果看不懂或你还搞不定就留下言

Answer 4

select 'A',a.P2
from (select count(1) P2 from t where t.t2 like '%A%' ) a
union
select 'B',b.P2
from (select count(1) P2 from t where t.t2 like '%B%' ) b
union
..........
最好调用存储过程