请教sql写法

显示全部楼层 · 2010-11-15 10:46:13

写有一条数据
用户id 用户权限
1
2,3,4,5,6
想要结果
用户id 用户权限
1
2
1
3
1
4
1
5
1
6
请教sql如何写？

千问 · 2010-11-15 10:46:13

9i replace+connect by
10g以上 regexp_substr+connect by
这种事情放在应用层做其实挺合适的，一个split成了一个数组，用起来方便

千问 · 2010-11-15 10:46:13

本帖最后由 lastwinner 于 2013-1-10 16:37 编辑
with t as (select '1,2,3,4,5' s from dual),
l as (select regexp_count(s,',') cnt from t),
u as (select level lv from l connect by level<=cnt+1),
v as (select to_number(regexp_substr(s,'\d+',1,lv)) aa from t,u, l)
select aa from v;
————————————————————————
AA
1
2
3
4
5

千问 · 2010-11-15 10:46:13

select * from (
with a as(
select 1 id,'2,3,4,5,6' auth from dual
)
SELECT id,decode(B,0,substr(auth,C),substr(auth,C,B-C)) auth FROM
(
SELECT id,auth,B,(lag(B,1,0) over(order by LV))+1 C
FROM(
SELECT id,auth,instr(auth,',',1,level) B,level LV from A
CONNECT BY level <=(length(auth) - length(replace(auth,',','')))+1
)
)
)

千问 · 2010-11-15 10:46:13

本帖最后由 qqwo2011 于 2013-1-14 14:11 编辑
以上方法都很不错