with tmp_t as
(select '1001' as userId, '10,12,15' as workgroups
from dual
union
select '1002' as userId, '2,4,5' as workgroups from dual)
select userid,
substr(tempgroups,
instr(tempgroups, ',', 1, lv) + 1,
instr(tempgroups, ',', 1, lv + 1) -
(instr(tempgroups, ',', 1, lv) + 1))
from (select userid,
',' || workgroups || ',' AS tempgroups,
length(workgroups || ',') -
nvl(length(replace(workgroups, ',')) , 0) AS groupcount
FROM tmp_t) a,
(select LEVEL lv from dual CONNECT BY LEVEL <= 5) b
where b.lv <= a.groupcount
order by userid, lv
结果为:
使用了CONNECT BY 构造1-5的lv值和instr截取,详细分析见原文。
个人看到connect by level <=5总想这替换掉5,只要得到表中最大的,个数就可以了,如下:
with tmp_t as
(select '1001' as userid, '10,12,15' as workgroups
from dual
union
select '1002' as userid, '2,4,5' as workgroups from dual)
select userid,
substr(tempgroups,
instr(tempgroups, ',', 1, lv) + 1,
instr(tempgroups, ',', 1, lv + 1) -
(instr(tempgroups, ',', 1, lv) + 1))
from (select userid,
',' || workgroups || ',' as tempgroups,
length(workgroups || ',') -
nvl(length(replace(workgroups, ',')) , 0) as groupcount
from tmp_t) a,
(select level lv
from dual
connect by level <= (select max(length(workgroups) -
length(replace(workgroups, ',', ''))) + 1
from tmp_t)) b
where b.lv <= a.groupcount
order by userid, lv
更好的写法如下:
with tmp_t as
(select '1001' as userid, '10,12,15' as workgroups
from dual
union
select '1002' as userid, '2,4,5' as workgroups from dual)
select userid,
--regexp_substr(str,reg,起始位置 第几次)
regexp_substr(workgroups, '[^,]+', 1, level) clr
from tmp_t
connect by level <= regexp_count(workgroups, ',') + 1
--regexp_count(workgroups, ',') 统计字符串中,的数量
--用法参考http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions135.htm#SQLRF51665
and userid = prior userid
and prior dbms_random.value is not null
相当简洁,很不错的写法,结果和上面的一样。
全文完。