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

Question

有一列数据，有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写出来???

Answer 1

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

Answer 2

嵌套再SQL 里面就好了例如：
select str,count(*)
(select （select regexp_substr(str,'[^;]+',1,rownum)  as str
   from dual connect by rownum<=5）
from tb)
group by str

Answer 3

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;

Answer 4

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

Answer 5

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的问题吧

Answer 6

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

Answer 7

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 straa 2
bb 3
cc 4
dd 1
ee 1
ff 1

Answer 8

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