with
a
as
(
select 1 id,'NAME1' name          ,0  pid from dual
union all
select 2 ,'NAME2'             ,1 from dual
union all
select 3, 'NAME3 '          , 2 from dual
union all
select 4 ,'NAME4'             , 3 from dual
union all
select 5 ,'NAME5'             , 4 from dual
union all
select 6 ,'NAME6'             , 0 from dual
union all
select 7 ,'NAME7 '          , 6 from dual
),
b
as
(
select id,name,pid,level flag,connect_by_root id rn
from a
start with pid=0
connect by prior id=pid
)
select id,name,pid,flag,rn,
lag(id) over(partition by rn order by flag) C1ID,
lag(name) over(partition by rn order by flag) C1NAME,
lag(id, 2) over(partition by rn order by flag) C2ID,
lag(name, 2) over(partition by rn order by flag) C2NAME,
lag(id, 3) over(partition by rn order by flag) C3ID,
lag(name, 3) over(partition by rn order by flag) C3NAME,
lag(id, 4) over(partition by rn order by flag) C4ID,
lag(name, 4) over(partition by rn order by flag) C4NAME
from b

ITPub博客

lag

最新文章