oracle olap函数(续)

本文介绍如何使用 Oracle SQL 中的 connect by 函数进行树状数据的遍历,包括从根节点到叶节点及从叶节点返回根节点的查询方式,并演示 lead 和 lag 函数获取记录的前后相邻行。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

/*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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值