Oracle数据库递归查询
最近在做一个树状编码管理系统，其中用到了oracle的树状递归查询（关键字：SELECT ... WHERE... START WITH ... CONNECT BY PRIOR） 以后开发树状菜单、树状评论、树状文件结构，只要和树扯得上关系的都可以应用，前提是你用的是oracle数据库
数据准备
-- 建表
CREATE TABLE TB (
ID NUMBER(10) NOT NULL, --主键
PID NUMBER(10) , --父id
NAME VARCHAR(128) --名称
)
-- 插数据
-- 一级节点
INSERT INTO TB VALUES (1,0,'查询');
INSERT INTO TB VALUES (2,0,'咨询');
INSERT INTO TB VALUES (3,0,'办理');
-- 二级节点
INSERT INTO TB VALUES (4,1,'余额查询');
INSERT INTO TB VALUES (5,1,'话费查询');
INSERT INTO TB VALUES (6,1,'城市查询');
INSERT INTO TB VALUES (7,1,'租房查询');
INSERT INTO TB VALUES (8,1,'公交查询');
INSERT INTO TB VALUES (9,1,'地铁查询');
INSERT INTO TB VALUES (10,2,'疑问咨询');
INSERT INTO TB VALUES (11,2,'报障咨询');
INSERT INTO TB VALUES (12,2,'话费咨询');
INSERT INTO TB VALUES (13,2,'余额咨询');
INSERT INTO TB VALUES (14,2,'活动咨询');
INSERT INTO TB VALUES (15,3,'公交办理');
INSERT INTO TB VALUES (16,3,'地铁办理');
INSERT INTO TB VALUES (17,3,'银行办理');
-- 三级节点
INSERT INTO TB VALUES (18,7,'一手房东查询');
INSERT INTO TB VALUES (19,7,'二手房东查询');
INSERT INTO TB VALUES (20,7,'三手房东查询');
INSERT INTO TB VALUES (21,7,'中介房东查询');以上数据PID为0的表示根节点，根节点可以有多个，根节点的PID最好不要用NULL，此时会引起全表扫描。 按照层级关系展示如下：
ID |父ID|层级|名称 |
---|----|----|-------------------|
1 |0 |1 |--查询 |
4 |1 |2 |----余额查询 |
5 |1 |2 |----话费查询 |
6 |1 |2 |----城市查询 |
7 |1 |2 |----租房查询 |
18 |7 |3 |------一手房东查询 |
19 |7 |3 |------二手房东查询 |
20 |7 |3 |------三手房东查询 |
21 |7 |3 |------中介房东查询 |
8 |1 |2 |----公交查询 |
9 |1 |2 |----地铁查询 |
2 |0 |1 |--咨询 |
10 |2 |2 |----疑问咨询 |
11 |2 |2 |----报障咨询 |
12 |2 |2 |----话费咨询 |
13 |2 |2 |----余额咨询 |
14 |2 |2 |----活动咨询 |
3 |0 |1 |--办理 |
15 |3 |2 |----公交办理 |
16 |3 |2 |----地铁办理 |
17 |3 |2 |----银行办理 |查找根节点
SELECT * FROM TB WHERE PID = 0;
ID |PID |NAME |
---|----|-----|
3 |0 |办理 |
1 |0 |查询 |
2 |0 |咨询 |查找某节点一级子节点
查询ID为1的节点的儿子
SELECT * FROM TB WHERE PID = 1;
ID |PID |NAME |
---|----|---------|
4 |1 |余额查询 |
5 |1 |话费查询 |
6 |1 |城市查询 |
7 |1 |租房查询 |
8 |1 |公交查询 |
9 |1 |地铁查询 |查询某节点直系父节点
SELECT * FROM TB C,TB P WHERE C.PID=P.ID AND C.ID=20;
ID |PID |NAME |ID |PID |NAME |
---|----|-------------|---|----|---------|
20 |7 |三手房东查询 |7 |1 |租房查询 |查询某节点所有兄弟节点
查询ID为6的节点的所有亲兄弟节点
SELECT * FROM TB A WHERE EXISTS (SELECT ID FROM TB B WHERE A.PID=B.PID AND B.ID=6);
ID |PID |NAME |
---|----|---------|
9 |1 |地铁查询 |
8 |1 |公交查询 |
7 |1 |租房查询 |
6 |1 |城市查询 |
5 |1 |话费查询 |
4 |1 |余额查询 |查找某节点所有子节点（自顶向下的树状）
从ID为1的节点开始，查询所有属于它的子节点，包括儿子，儿子的儿子，儿子的儿子的儿子，儿子的儿子的儿子....无限个儿子
SELECT * FROM TB START WITH ID = 1 CONNECT BY PRIOR ID=PID;
ID |PID |NAME |
---|----|------------|
1 |0 |查询 |
4 |1 |余额查询 |
5 |1 |话费查询 |
6 |1 |城市查询 |
7 |1 |租房查询 |
18 |7 |一手房东查询|
19 |7 |二手房东查询|
20 |7 |三手房东查询|
21 |7 |中介房东查询|
8 |1 |公交查询 |
9 |1 |地铁查询 |当然，你也可以加WHERE条件，不要名称中含有房东的儿子节点
SELECT * FROM TB WHERE NAME NOT LIKE '%房东%' START WITH ID = 1 CONNECT BY PRIOR ID=PID;
ID |PID |NAME |
---|----|---------|
1 |0 |查询 |
4 |1 |余额查询 |
5 |1 |话费查询 |
6 |1 |城市查询 |
7 |1 |租房查询 |
8 |1 |公交查询 |
9 |1 |地铁查询 |甚至可以指定多个根节点
SELECT * FROM TB START WITH ID IN (1,3) CONNECT BY PRIOR ID=PID;
ID |PID |NAME |
---|----|-------------|
1 |0 |查询 |
4 |1 |余额查询 |
5 |1 |话费查询 |
6 |1 |城市查询 |
7 |1 |租房查询 |
18 |7 |一手房东查询 |
19 |7 |二手房东查询 |
20 |7 |三手房东查询 |
21 |7 |中介房东查询 |
8 |1 |公交查询 |
9 |1 |地铁查询 |
3 |0 |办理 |
15 |3 |公交办理 |
16 |3 |地铁办理 |
17 |3 |银行办理 |查找某节点所有父节点（自下向上的树状）
这个和上面查找某节点所有子节点（自顶向下的树状）的唯一区别就是PID和ID的位置交换了，上面能用的WHERE和IN这里也能用，不再赘述
SELECT * FROM TB START WITH ID = 18 CONNECT BY PRIOR PID=ID;
ID |PID |NAME |
---|----|-------------|
18 |7 |一手房东查询 |
7 |1 |租房查询 |
1 |0 |查询 |查询同一层级的所有节点
不管节点是属于哪个根节点的，只要在同一层级都可以查询出来，和查询某节点所有兄弟节点的不同之处在于，前者是查询亲兄弟，后者是所有兄弟，不管是亲兄弟，堂兄弟，表兄弟 用临时表保存层级信息（LEAF），然后从临时表中查询传入ID的层级，最后查询所有在同一层级的节点
WITH TMP AS( SELECT A.*, LEVEL LEAF
FROM TB A START WITH A.PID = 0 CONNECT BY PRIOR A.ID = A.PID)
SELECT * FROM TMP
WHERE LEAF = ( SELECT LEAF FROM TMP WHERE ID = 7);
ID |PID |NAME |LEAF |
---|----|---------|-----|
4 |1 |余额查询 |2 |
5 |1 |话费查询 |2 |
6 |1 |城市查询 |2 |
7 |1 |租房查询 |2 |
8 |1 |公交查询 |2 |
9 |1 |地铁查询 |2 |
10 |2 |疑问咨询 |2 |
11 |2 |报障咨询 |2 |
12 |2 |话费咨询 |2 |
13 |2 |余额咨询 |2 |
14 |2 |活动咨询 |2 |
15 |3 |公交办理 |2 |
16 |3 |地铁办理 |2 |
17 |3 |银行办理 |2 |可以看到，上面的所有节点都是2层级，本文最开始的层级关系展示图可以用下面的SQL查询出来，LPAD函数可适当调整
WITH TMP AS( SELECT A.*, LEVEL LEAF
FROM TB A START WITH A.PID = 0 CONNECT BY PRIOR A.ID = A.PID)
SELECT ID,PID AS 父ID,LEAF AS 层级,LPAD(NAME,LEAF * 6,'-') AS 名称
FROM TMP查询每个节点所属的分类（正向递归嵌套反向递归）
此查询在对每个节点进行分类和其他表联查的时候比较有用
SELECT A.ID,A.NAME,
-- 在正向递归的基础上反向（自下向上）递归，找到每个子节点PID为0的那个父节点（大分类）的ID
(SELECT B.ID FROM TB B WHERE B.PID = 0 START WITH B.ID = A.ID CONNECT BY PRIOR B.PID = B.ID) AS RID,
-- 在正向递归的基础上反向（自下向上）递归，找到每个子节点PID为0的那个父节点（大分类）的名称
(SELECT B.NAME FROM TB B WHERE B.PID = 0 START WITH B.ID = A.ID CONNECT BY PRIOR B.PID = B.ID) AS RNAME
-- 正向（自顶向下）递归，A.PID != 0排除了最外层的大类分类
FROM TB A WHERE A.PID != 0 START WITH A.PID = 0 CONNECT BY PRIOR A.ID = A.PID
ID |NAME |RID |RNAME|
---|-------------|----|-----|
4 |余额查询 |1 |查询 |
5 |话费查询 |1 |查询 |
6 |城市查询 |1 |查询 |
7 |租房查询 |1 |查询 |
18 |一手房东查询 |1 |查询 |
19 |二手房东查询 |1 |查询 |
20 |三手房东查询 |1 |查询 |
21 |中介房东查询 |1 |查询 |
8 |公交查询 |1 |查询 |
9 |地铁查询 |1 |查询 |
10 |疑问咨询 |2 |咨询 |
11 |报障咨询 |2 |咨询 |
12 |话费咨询 |2 |咨询 |
13 |余额咨询 |2 |咨询 |
14 |活动咨询 |2 |咨询 |
15 |公交办理 |3 |办理 |
16 |地铁办理 |3 |办理 |
17 |银行办理 |3 |办理 |
其他查询
- 自顶向下路径查询
SELECT SYS_CONNECT_BY_PATH (NAME, '/') AS PATH FROM TB
WHERE ID = 18 START WITH PID = 0 CONNECT BY PRIOR ID=PID;
PATH |
----------------------------|
/查询/租房查询/一手房东查询 |- 自下向上路径查询 注意和自顶向下的在效率上的区别，引用别人的一句话
在这里我又不得不放个牢骚了。oracle只提供了一个sys_connect_by_path函数，却忘了字符串的连接的顺序。在上面的例子中，第一个sql是从根节点开始遍历，而第二个sql是直接找到当前节点，从效率上来说已经是千差万别，更关键的是第一个sql只能选择一个节点，而第二个sql却是遍历出了一颗树来。再次ps一下。https://www.cnblogs.com/linjiqin/p/3152674.html
SELECT SYS_CONNECT_BY_PATH (NAME, '/') AS PATH FROM TB
START WITH ID = 18 CONNECT BY PRIOR PID=ID;
PATH |
----------------------------|
/一手房东查询 |
/一手房东查询/租房查询 |
/一手房东查询/租房查询/查询 |- 查询树状始终显示根节点
SELECT CONNECT_BY_ROOT NAME,TB.* FROM TB START WITH ID = 7 CONNECT BY PRIOR ID = PID;
CONNECT_BY_ROOTNAME |ID |PID |NAME |
------------------------|---|----|-------------|
租房查询 |7 |1 |租房查询 |
租房查询 |18 |7 |一手房东查询 |
租房查询 |19 |7 |二手房东查询 |
租房查询 |20 |7 |三手房东查询 |
租房查询 |21 |7 |中介房东查询 |
- 动态查询是否是叶子节点，是叶子节点表示该节点没有儿子了，否则有儿子，ORACLE自带的CONNECT_BY_ISLEAF能动态显示是否叶子节点，1是0否
SELECT CONNECT_BY_ISLEAF AS IS_LEAF,TB.*
FROM TB START WITH ID = 1 CONNECT BY PRIOR ID = PID;
IS_LEAF |ID |PID |NAME |
--------|---|----|-------------|
0 |1 |0 |查询 |
1 |4 |1 |余额查询 |
1 |5 |1 |话费查询 |
1 |6 |1 |城市查询 |
0 |7 |1 |租房查询 |
1 |18 |7 |一手房东查询 |
1 |19 |7 |二手房东查询 |
1 |20 |7 |三手房东查询 |
1 |21 |7 |中介房东查询 |
1 |8 |1 |公交查询 |
1 |9 |1 |地铁查询 |
相关推荐
-
ipad电池选项里没有电池健康（ipad设置里面没有电池健康）
相信很多刚买了iPad的消费者都有一个疑问：明明iPhone可以随时查看电池寿命，为什么iPad就没有电池健康的选项呢？ 黑猫在用的平板电脑型号是iPad 2018，至今已经服役3年，目前用的系统是iPad OS 15.3。和大家一样，...
-
红豆怎么买（红豆怎么分辨真假）
今日导读：我们在购买红豆的时候需要注意什么呢？ 今天我们来说说看，红豆！说起这个红豆呢，我就首先想到，红豆雪糕！哈哈因为我今天刚吃过一根，味道还是和以前一模一样。然后就是红豆粽子，也是比喜欢吃的，但是...
-
警察一级警督相当于什么级别（请问一级警督是什么级别）
正常公司内部从职员到经理，到总经理而逐步递增；医疗机关内由助理医师到职业医师，到主任医师而逐步提高，那么在公安机关内部呢？ 最常提到的警察无非是片警，交警，刑警……那么“一级警督”是什么级别的警察呢？ 中国...
-
abcc的四字词语有哪些（ABCC式词语大全100个）
ABCC形成语积累 傲骨嶙嶙 白发苍苍 不过尔尔 波光粼粼 波光鳞鳞 薄暮冥冥 板上钉钉 不甚了了 白雪皑皑 此恨绵绵 长夜漫漫 大才盘盘 大才盘盘 大腹便便 谠论侃侃 大名鼎鼎 颠毛种种 钉头磷磷 独行踽踽 得意洋洋 得意...
-
炖羊肉汤放什么香料喝着不腥（就3种调料,其他别乱放）
进入寒冷的冬天以后，你家里有人在对羊肉进行食用吗？作为一种性温的肉类食材，其与很多肉类食材进行对比的时候，冬天吃羊肉的话，就能对我们的身体，提供丰富的热量，让我们整个冬天都暖暖的。 然而羊肉是一种非常...
-
徐霞客的资料简介（徐霞客的一生简介）
徐霞客（1587年1月5日－1641年3月8日），名弘祖，字振之，号霞客，南直隶江阴县（今江苏省江阴市）人，明代地理学家、旅行家和文学家，他经30年考察撰成了60万字地理名著《徐霞客游记》，被称为“千古奇人”。 徐霞客...