在数据库中, 有一种比较常见得设计模式, 层级结构设计模式
--创建测试表和数据
create table DEMO (ID varchar2(10) primary key, DSC varchar2(100), PID varchar2(10));
Insert Into DEMO values ('00001', '中国', '-1');
Insert Into DEMO values ('00011', '辽宁', '00001');
Insert Into DEMO values ('00012', '贵州', '00001');
Insert Into DEMO values ('00013', '河南', '00001');
Insert Into DEMO values ('00111', '鞍山', '00011');
insert Into DEMO values ('00112', '沈阳', '00011');
Insert Into DEMO values ('00113', '抚顺', '00011');
--语法
select * from [YOU TABLE]
START WITH 条件1
CONNECT BY 条件2
条件1: 表示从哪个节点开始查找, 也就是通过条件1 查询到的数据, 作为后续查询的起始节点(参数).
当然可以放宽限定条件,如 ID in ('00001', '00011')以取得多个根节点,也就是多棵树;
条件2: 是连接条件,其中用PRIOR表示上一条记录,例如CONNECT BY PRIOR ID = PID
意思就是本条记录的ID是下一条记录的PID. (把当前的ID作为下一次查询的PID继续找下一级)。
注意:CONNECT BY PRIOR PID = ID 这样就是反向查询 找到他的所有上级数据
--查找最父级中国的所有子节点
select * from DEMO t
START with t.id = 00001
connect by PRIOR t.id = t.pid
--查找“辽宁”的所有子节点
select * from DEMO t
START with t.id = 00011
connect by PRIOR t.id = t.pid
下面是几条关键字特殊点::
1.nocycle关键字可消除循环; 有时候数据本身 不合理会导致出现循环的问题,
如将上述的 ID ‘00011’ 记录的 ‘PID’ 也改为 ‘00011’, 会出现循环的问题
Connect By nocycle Prior ID = PID 即可
2.connect_by_isleaf 表示当前节点是否是叶子节点
3.level 表示当前节点所处层级, 这里的层级指的是 从 start with 查询到的节点(1)开始往下算起, 当前属于第几层级。
select
t.*,
LEVEL,
CONNECT_BY_ISLEAF
from DEMO t
START with t.id = 00011
connect by nocycle PRIOR t.id = t.pid