Oracle Start With关键字
Start With (树查询)
问题描述:
在数据库中, 有一种比较常见得 设计模式, 层级结构 设计模式, 具体到 Oracle table中, 字段特点如下:
ID, DSC, PID;
三个字段, 分别表示 当前标识的 ID(主键), DSC 当前标识的描述, PID 其父级ID, 比较典型的例子 是 国家, 省, 市 这种层级结构;
省份归属于国家, 因此 PID 为 国家的 ID, 以此类推;
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');
这样子就成了一个简单的树级结构, 我一般将 根节点的 PID 定为 -1;
Start With:
参考链接: http://blog.csdn.net/weiwenhp/article/details/8218091
基本语法如下:
SELECT ... FROM + 表名 WHERE + 条件3 START WITH + 条件1 CONNECT BY PRIOR + 条件2 --示例 Select * From DEMO Start With ID = '00001' Connect By Prior ID = PID
条件1: 表示从哪个节点开始查找, 也就是通过条件1 查询到的数据, 作为后续查询的起始节点(参数).
当然可以放宽限定条件，如 ID in ('00001', '00011')以取得多个根节点，也就是多棵树；在连接关系中，除了可以使用列明外，还允许使用列表达式。
如果省略Start With
就默认把所有满足查询条件的Tree整个表中的数据从头到尾遍历一次,每一个数据做一次根,然后遍历树中其他节点信息.
条件2: 是连接条件，其中用PRIOR表示上一条记录，例如CONNECT BY PRIOR ID = PID，意思就是上一条记录的ID是本条记录的PID，即本记录的父亲是上一条记录。CONNECT BY子句说明每行数据将是按照层次顺序检索，并规定将表中的数据连入树形结构的关系中。
Prior 在父节点的一侧表示, 自底向上查, 在 子节点的一侧表示 自上向下查询;
条件3: 不能用在 Connect By 后, 这里的条件判断, 等价于 在最后查询出结果列表之后, 再进行条件筛选; 并非 删除掉 节点及子节点;
--自底向上 Select * From DEMO Start With ID = '00113' Connect By Prior PID = ID --结果 00113 延安 00011 00011 陕西 00001 00001 中国 -1 --自上向下 Select * From DEMO Start With ID = '00001' --用 Start Wiht PID = '-1' 结果不变 Connect By Prior ID = PID --结果 00001 中国 -1 00011 陕西 00001 00111 西安 00011 00112 咸阳 00011 00113 延安 00011 00012 贵州 00001 00013 河南 00001 --Where 删除 Select ID, PID, DSC From DEMO WHERE ID <> '00011' Start With ID = '00001' Connect By Prior ID = PID --结果 00001 -1 中国 00111 00011 西安 00112 00011 咸阳 00113 00011 延安 00012 00001 贵州 00013 00001 河南
下面是几条关键字特殊点:
nocycle关键字, 有时候数据本身 不合理会导致出现循环的问题, 如 将上述的 ID '00001' 记录的 'PID' 也改为 '00001', 会出现循环的问题, 这是, 需要用到 nocycle 即可消除循环;
Connect By nocycle Prior ID = PID 即可.
connect_by_isleaf 表示当前节点是否是叶子节点
level 表示当前节点所处层级, 这里的层级指的是 从 start with 查询到的节点开始往下算起, 当前属于第几层级
Select ID, PID, DSC, connect_by_isleaf isLeaf, LEVEL From DEMO Connect By nocycle Prior ID = PID Start With ID = '00001'; --结果 ID PID DSC isLeaf LEVEL 00001 00001 中国 0 0 00011 00001 陕西 0 1 00111 00011 西安 1 2 00112 00011 咸阳 1 2 00113 00011 延安 1 2 00012 00001 贵州 1 1 00013 00001 河南 1 1
这里需要注意的一个点, 如果采用的是 自底向上的 方式查询, 则 LEVEL 的 层级 同样是 从底向上, 如 00113 LEVEL 1 00011 LEVEL 2 00001 LEVEL 3.
另外一点: 如果在查询语句中 Select ID, PID, DSC, connect_by_isleaf isLeaf, LEVEL - 1 LEVEL 这种查询方式的话, 在 WHERE 判断条件中, 只需要判断 LEVEL = 1, 就可以取出 当前查询节点的 子节点(由于LEVEL 也是 伪列, 需要用子查询的方式);
SIBLINGS关键字:它会保护层次，并且在每个等级中按expre排序。
Select ID, PID, DSC, connect_by_isleaf, LEVEL From DEMO Start With ID = '00001' Connect By nocycle Prior ID = PID ORDER By DSC --结果, 仅贴出部分数据(层级结构被破坏了) 00012 00001 贵州 1 2 00013 00001 河南 1 2 00011 00001 陕西 0 2 00111 00011 西安 1 3 00112 00011 咸阳 1 3 00113 00011 延安 1 3 00001 -1 中国 0 1 --ORDER SIBLINGS By DSC Select ID, PID, DSC, connect_by_isleaf, LEVEL From DEMO Start With ID = '00001' Connect By nocycle Prior ID = PID ORDER SIBLINGS By DSC --结果(Level 层级不变) 00001 -1 中国 0 1 00012 00001 贵州 1 2 00013 00001 河南 1 2 00011 00001 陕西 0 2 00111 00011 西安 1 3 00112 00011 咸阳 1 3 00113 00011 延安 1 3
connect_by_iscycle:存在循环，将返回1，否则返回0
