oracle olap函数（续）

最新推荐文章于 2025-08-23 23:16:00 发布

原创最新推荐文章于 2025-08-23 23:16:00 发布 · 416 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #insert #table #date #byte

本文介绍如何使用 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