写了一个,嵌套了好多层,楼主自己整理下吧。with t as ( select '1' x, '11,12' z from dual union all select '2' x, '10,11' z from dual union all select '1' x, '11,13' z from dual union all select '2' x, '12,13,10' z from dual ), r as ( select rownum r from dual connect by level <= 3 ) select c,sum(cu2),sum(cu1) from (select c,decode(x,1,cu,0) cu1,decode(x,2,cu,0) cu2 from (select c,x,count(*) cu from(select x,z,r,ltrim(s,',') c from (select x,z,r,regexp_substr(','||z, ',[^,]*',1,r) s from t,r) where s is not null ) group by c,x)) group by c order by c;
select z as 结果, sum(decode(x, 1, 1, 0)) as 数量1, sum(decode(x, 2, 1, 0)) as 数量2 from (select x, regexp_substr(z, '[^,]+', 1, rownum) as z from tabl1 connect by rownum <= length(regexp_replace(z, '[^,]+')) + 1)) group by z
别总是纠结于用一个sql实现
(
select '1' x, '11,12' z from dual union all
select '2' x, '10,11' z from dual union all
select '1' x, '11,13' z from dual union all
select '2' x, '12,13,10' z from dual
),
r as
(
select rownum r from dual connect by level <= 3
)
select c,sum(cu2),sum(cu1) from (select c,decode(x,1,cu,0) cu1,decode(x,2,cu,0) cu2 from (select c,x,count(*) cu from(select x,z,r,ltrim(s,',') c from (select x,z,r,regexp_substr(','||z, ',[^,]*',1,r) s from t,r) where s is not null ) group by c,x)) group by c order by c;
select z as 结果,
sum(decode(x, 1, 1, 0)) as 数量1,
sum(decode(x, 2, 1, 0)) as 数量2
from (select x, regexp_substr(z, '[^,]+', 1, rownum) as z
from tabl1
connect by rownum <= length(regexp_replace(z, '[^,]+')) + 1))
group by z