IT忍者神龟之oracle在分组内排序的方法回顾(二)
E values('Case1','Step1',to_date('20070201','yyyy-mm-dd')); insert into LEAD_TABLE values('Case2','Step2',to_date('20070202','yyyy-mm-dd')); insert into LEAD_TABLE values('Case2','Step3',to_date('20070203','yyyy-mm-dd')); commit; 结果如下： Case1 Step1 2007-1-1 Step2 2007-1-2 Case1 Step2 2007-1-2 Step3 2007-1-3 Step1 2007-1-1 Case1 Step3 2007-1-3 Step4 2007-1-4 Step2 2007-1-2 Case1 Step4 2007-1-4 Step5 2007-1-5 Step3 2007-1-3 Case1 Step5 2007-1-5 Step4 2007-1-6 Step4 2007-1-4 Case1 Step4 2007-1-6 Step6 2007-1-7 Step5 2007-1-5 Case1 Step6 2007-1-7 Step4 2007-1-6 Case2 Step1 2007-2-1 Step2 2007-2-2 Case2 Step2 2007-2-2 Step3 2007-2-3 Step1 2007-2-1 Case2 Step3 2007-2-3 Step2 2007-2-2 还可以进一步统计一下两者的相差天数 select caseid,stepid,actiondate,nextactiondate,nextactiondate-actiondate datebetween from ( select caseid,stepid,actiondate,lead(stepid) over (partition by caseid order by actiondate) nextstepid, lead(actiondate) over (partition by caseid order by actiondate) nextactiondate, lag(stepid) over (partition by caseid order by actiondate) prestepid, lag(actiondate) over (partition by caseid order by actiondate) preactiondate from lead_table) 结果如下： Case1 Step1 2007-1-1 2007-1-2 1 Case1 Step2 2007-1-2 2007-1-3 1 Case1 Step3 2007-1-3 2007-1-4 1 Case1 Step4 2007-1-4 2007-1-5 1 Case1 Step5 2007-1-5 2007-1-6 1 Case1 Step4 2007-1-6 2007-1-7 1 Case1 Step6 2007-1-7 Case2 Step1 2007-2-1 2007-2-2 1 Case2 Step2 2007-2-2 2007-2-3 1 Case2 Step3 2007-2-3 每一条记录都能连接到上/下一行的内容 lead （） 下一个值 lag（） 上一个值 select caseid,stepid,actiondate,lead(stepid) over (partition by caseid order by actiondate) nextstepid, lead(actiondate) over (partition by caseid order by actiondate) nextactiondate, lag(stepid) over (partition by caseid order by actiondate) prestepid, lag(actiondate) over (partition by caseid order by actiondate) preactiondate from lead_table