oracle olap函数（续）

/*connect by函数*/

CREATE TABLE TBL_TEST
(
  ID    NUMBER,
  NAME  VARCHAR2(100 BYTE),
  PID   NUMBER                                  DEFAULT 0
);


INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('6','126','2');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('7','127','1');
commit;

--从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid
order by pid,id


--从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid=id

 

 

/*lead下一个  lag上一个*/
create table LEAD_TABLE
(
  CASEID     VARCHAR2(10),
  STEPID     VARCHAR2(10),
  ACTIONDATE DATE
);
  insert into LEAD_TABLE values('Case1','Step1',to_date('20070101','yyyy-mm-dd'));
  insert into LEAD_TABLE values('Case1','Step2',to_date('20070102','yyyy-mm-dd'));
  insert into LEAD_TABLE values('Case1','Step3',to_date('20070103','yyyy-mm-dd'));
  insert into LEAD_TABLE values('Case1','Step4',to_date('20070104','yyyy-mm-dd'));
  insert into LEAD_TABLE values('Case1','Step5',to_date('20070105','yyyy-mm-dd'));
  insert into LEAD_TABLE values('Case1','Step4',to_date('20070106','yyyy-mm-dd'));
  insert into LEAD_TABLE values('Case1','Step6',to_date('20070101','yyyy-mm-dd'));
  insert into LEAD_TABLE 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;

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