with t as (select 13933691234 num from dual union all select 13933695689 from dual union all select 13933693210 from dual union all select 13936972149 from dual union all select 13015692248 from dual union all select 13349212345 from dual),
tmp as (select a.num, b.rn, to_number(substr(a.num, b.rn, 1)) i from t a, (select rownum rn from dual connect by rownum <= 11) b) select x.num, y.str from t x, (select num, wm_concat(replace(str, ',')) str from (select num, max(sys_connect_by_path(i, ',')) str from tmp x where level >= 4 start with not exists (select 1 from tmp where num = x.num and rn = x.rn - 1 and i = x.i - 1) connect by prior num = num and prior rn = rn - 1 and prior i = i - 1 group by num, connect_by_root rn union all select num, max(sys_connect_by_path(i, ',')) str from tmp x where level >= 4 start with not exists (select 1 from tmp where num = x.num and rn = x.rn - 1 and i = x.i + 1) connect by prior num = num and prior rn = rn - 1 and prior i = i + 1 group by num, connect_by_root rn) group by num) y where x.num = y.num(+)
(select 13933691234 num
from dual
union all
select 13933695689
from dual
union all
select 13933693210
from dual
union all
select 13936972149
from dual
union all
select 13015692248
from dual
union all
select 13349212345 from dual),
tmp as
(select a.num, b.rn, to_number(substr(a.num, b.rn, 1)) i
from t a, (select rownum rn from dual connect by rownum <= 11) b)
select x.num, y.str
from t x,
(select num, wm_concat(replace(str, ',')) str
from (select num, max(sys_connect_by_path(i, ',')) str
from tmp x
where level >= 4
start with not exists (select 1
from tmp
where num = x.num
and rn = x.rn - 1
and i = x.i - 1)
connect by prior num = num
and prior rn = rn - 1
and prior i = i - 1
group by num, connect_by_root rn
union all
select num, max(sys_connect_by_path(i, ',')) str
from tmp x
where level >= 4
start with not exists (select 1
from tmp
where num = x.num
and rn = x.rn - 1
and i = x.i + 1)
connect by prior num = num
and prior rn = rn - 1
and prior i = i + 1
group by num, connect_by_root rn)
group by num) y
where x.num = y.num(+)
本来是要实现图中号码分层的功能,通过号码规则获取层级。不知前辈有没有好的办法