ORACLE-你是英雄你来挑战

[us_yunleiwangdb]

这是一个文本，里面是记录信息
188888,0,13
188888,1,214
188888\,2000000,0,1
188888\,2000000,1,2
188888\,2000000,1,1
188888\,2000000\,300000,1,2
188888\,200000\,300000\,400000,1,1
188888\,200000\,300000\,400000\,500000,1,1
188888\,200000\,300000\,400000\,500000\,600000,1,1
188888\,200000\,300000\,400000\,500000\,600000\,700000,1,1
188888\,200000\,300000\,400000\,500000\,600000\,700000\,800000,1,1
188888\,200000\,300000\,400000\,500000\,600000\,700000\,800000\,900000,1,1
188888\,200000\,300000\,400000\,500000\,600000\,700000\,800000\,900000\,111111,1,1
188888\,200000\,300000\,400000\,500000\,600000\,700000\,800000\,900000\,111111\,122222,1,1

create table test_level(empno number(20),MGR NUMBER(20),SAL_A NUMBER(5),SAL_B NUMBER(5))

我想把上面的内容拆成下面格式---树状（188888 是根节点）

EMPNO,MGR,A_COUNT U_ACCOUNT(例如ORACLE--EMP表中 EMPNO,MGR 树状形式）
EMPNO MGR A_COUNT U_ACCOUNT
188888 '' 0 13 ---根节点
188888 '' 1 214 -- 跟节点
2000000 188888 0 1--下一级子节点
2000000 188888 1 2---下一级子节点
2000000 188888 1 1---下一级子节点
300000 200000 1 2 ----下一级子节点
400000 300000 1 1 ---下一级子节点
500000 400000 1 1 以此类推
600000 500000 1 1
700000 600000 1 1
800000 700000 1 1
900000 800000 1 1
111111 900000 1 1
122222 111111 1 1

[bw555]

假设原表为T，大文本字段为STR

select
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')) EMPNO,
decode(REGEXP_COUNT(str, '[0-9]+'),1,null,
        REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')-1)) MGR
,A_COUNT,U_ACCOUNT
from T

[bw555]

分真少，唉

[us_yunleiwangdb]

楼上的你没写全啊，EMPNO,MGR, A_COUNT , U_ACCOUNT --一从上面文本截取 要变成四个字段，

[卖水果的net]

-- 凑个人数，放到一个字段中，LZ  自己拆分吧。

with mt as (
select '188888,0,13' rn from dual union all
select '188888,1,214' from dual union all
select '188888\,2000000,0,1' from dual union all
select '188888\,2000000,1,2' from dual union all
select '188888\,2000000,1,1' from dual union all
select '188888\,2000000\,300000,1,2' from dual union all
select '188888\,200000\,300000\,400000,1,1' from dual union all
select '188888\,200000\,300000\,400000\,500000,1,1' from dual
) 

select 
reverse(substr(reverse(rn),0, (case when instr(reverse(rn),',',1,4) = 0 then 4000 else instr(reverse(rn),',',1,4) - 1  end  ) )) text
from mt

[bw555]

引用 3 楼 us_yunleiwangdb 的回复:

楼上的你没写全啊，EMPNO,MGR, A_COUNT , U_ACCOUNT --一从上面文本截取 要变成四个字段，

这回补齐了

select
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')-2) EMPNO,
decode(REGEXP_COUNT(str, '[0-9]+'),3,null,
        REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')-3)) MGR,
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')-1) EMPNO A_COUNT,
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')) EMPNO U_ACCOUNT
from T

[bw555]

复制是漏删了点，修正下

select
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')-2) EMPNO,
decode(REGEXP_COUNT(str, '[0-9]+'),3,null,
        REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')-3)) MGR,
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')-1) A_COUNT,
REGEXP_SUBSTR(str, '[0-9]+', 1,REGEXP_COUNT(str, '[0-9]+')) U_ACCOUNT
from T

[Jan丶X]