ITPub博客

首页 > Linux操作系统 > Linux操作系统 > lag

lag

原创 Linux操作系统 作者:Nalternative 时间:2011-05-10 15:37:45 0 删除 编辑
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博客 ” ,链接:http://blog.itpub.net/25323853/viewspace-694921/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-02-09

  • 博文量
    123
  • 访问量
    174507