oracle 分割字符串 regexp_substr，再分组求和

[wulong1105]

有一列数据，有6行记录，有2列 objID（主键） str 具体为：
objID str(列名)
1 aa,bb
2 aa,cc
3 bb,cc
4 bb,dd
5 cc,ee
6 cc,ff

要分割每行记录的str字段值，再分组求和，得到
str sum (求和)
aa 2
bb 3
cc 4
dd 1
ee 1
ff 1

我知道分割一行记录用类似于下面sql

   select regexp_substr('11;22;33;44;55;','[^;]+',1,rownum) 
   from dual connect by rownum<=5

其中 '11;22;33;44;55;' 代表一行记录，但我想要查询每行记录，分割再求和，请教各位哥哥妹妹们，怎么用1句sql写出来???

[cosio]

with temp as 
(
select   1,'aa' a,'bb' b from dual
union all
select    2,'aa','cc' from dual
union all
select    3,'bb','cc' from dual
union all
select    4,'bb','dd' from dual
union all
select   5,'cc','ee' from dual
union all
select   6,'cc','ff' from dual
)
select a,count(a)
 from
 (
  select a from temp
  union all
  select b from temp
  )
group by a


--result:
1	2	aa
2	3	bb
3	4	cc
4	1	dd
5	1	ee
6	1	ff

[iqlife]

嵌套再SQL 里面就好了

例如：
select str,count(*)
(select （select regexp_substr(str,'[^;]+',1,rownum) as str
from dual connect by rownum<=5）
from tb)
group by str

[lisong770818]

with a as
(

select 1 objID, 'aa,bb' str
from dual
union all
select 2, 'aa,cc'
from dual
union all
select 3, 'bb,cc'
from dual
union all
select 4, 'bb,dd'
from dual
union all
select 5, 'cc,ee' from dual)

select str, count(1)
from (select substr(str, 1, 2) str
from a
union all
select substr(str, 4, 5) from a)
group by str;

[momotou11]

select sum(NUM), STR
from (select count(*) NUM, STR
from (select REGEXP_SUBSTR(STR, '[^;]+', 1, ROWNUM) as STR
from TABLE_NAME --写上你的表名
connect by ROWNUM <= 1000) --这里设定的大一点，有的分出来的多
group by STR)
group by STR

[guoxin_min]

[Quote=引用 4 楼 momotou11 的回复:]

select sum(NUM), STR
from (select count(*) NUM, STR
from (select REGEXP_SUBSTR(STR, '[^;]+', 1, ROWNUM) as STR
from TABLE_NAME --写上你的表名
connect by RO……
[/Quote]


select regexp_substr('a,b,c,a,c', '[^,]', 1, level) as STR
from dual
connect by regexp_substr('a,b,c,a,c', '[^,]', 1, level) is not null;

可以避免ROWNUM的问题吧

[zzb13425138525]

with rs1 as ( select count(objID) objNum from myTable ) select newStr ,count(objID) from ( select t1.objID,regexp_substr(t1.str, '[^,]+', 1,rn) newStr from myTable t1, (select rownum rn from dual connect by rownum<=(select rs1.objNum from rs1)) where regexp_substr(t1.str, '[^,]+', 1,rn) is not null order by newStr ) group by newStr

[zzb13425138525]

以上是正解，我的业务需求也有涉及到这种情况，我就是这么做的

[qazwsxedcwys]

create table tt ( objID number , str varchar2(20) ) select tt.*,tt.rowid from tt ; select str ,count(*) from ( select objid ,str from ( select objid ,regexp_substr(str,'\w+',1,1) str from tt union all select objid ,regexp_substr(str,'\w+',1,2) str from tt where regexp_substr(str,'\w+',1,2) is not null union all select objid ,regexp_substr(str,'\w+',1,3) str from tt where regexp_substr(str,'\w+',1,3) is not null ) ) group by str aa 2 bb 3 cc 4 dd 1 ee 1 ff 1

[Persistence_x]

with tt as
(
select 1 objID, 'aa,bb' str from dual
union all
select 2, 'aa,cc' from dual
union all
select 3, 'bb,cc' from dual
union all
select 4, 'bb,dd' from dual
union all
select 5, 'cc,ee' from dual
union all 
select 6, 'cccdd,ff'from dual
)
select str ,count(*) from (

select objid ,str from (
select objid ,regexp_substr(str,'\w+',1,1) str from tt
union all
select objid ,regexp_substr(str,'\w+',1,2) str from tt where regexp_substr(str,'\w+',1,2) is not null 
union all
select objid ,regexp_substr(str,'\w+',1,3) str from tt where regexp_substr(str,'\w+',1,3) is not null)

) group by str