start with ... connect by ... prior 实例

来源：互联网发布：近视手术知乎编辑：程序博客网时间：2024/06/01 08:46

创建表的脚本及测试数据
CREATE TABLE TABLE1
(
UUID VARCHAR2(20 BYTE),
XUHAO VARCHAR2(20 BYTE),
PRICE NUMBER(10,2)
)

Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('001', '3', 140);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('002', '3.1', 140);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('003', '3.1.1', 50);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('004', '3.1.1.1', 20);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('005', '3.1.1.2', 30);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('006', '3.1.2', 90);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('007', '3.1.2.1', 40);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('008', '3.1.2.2', 50);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('009', '4', 136);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('010', '4.1', 70);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('011', '4.2', 66);
Insert into TABLE1
(UUID, XUHAO, PRICE)
Values
('012', '5', 90);
COMMIT;

得到所叶子及没有叶子的节点：

select * from table1 a
where connect_by_isleaf=1
start with length(xuhao)=1
connect by xuhao like prior xuhao||'.%'
and xuhao not like prior xuhao||'.%.%'