-- Start
递归查询的一个典型的例子是对树状结构的表进行查询,考虑如下的情况:
论坛首页
--数据库开发
----DB2
------DB2 文章1
--------DB2 文章1 的评论1
--------DB2 文章1 的评论2
------DB2 文章2
----Oracle
--Java 技术
以上是一个论坛的典型例子,下面我们新建一个表来存储以上信息。
CREATE TABLE BBS
(
PARENT_ID NUMBER(9, 0) NOT NULL,
ID NUMBER(9, 0) NOT NULL,
NAME VARCHAR2(200) NOT NULL -- 板块、文章、评论等。
);
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (0,0,'论坛首页');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (0,1,'数据库开发');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (1,11,'DB2');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (11,111,'DB2 文章1');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (111,1111,'DB2 文章1 的评论1');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (111,1112,'DB2 文章1 的评论2');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (11,112,'DB2 文章2');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (1,12,'Oracle');
INSERT INTO BBS (PARENT_ID,ID,NAME) VALUES (0,2,'Java 技术');
现在万事兼备了,我们开始查询吧。假设现在让你查询一下‘DB2 文章1’的所有评论,有人说,这还不简单,如下这样就可以了。
SELECT * FROM BBS WHERE PARENT_ID=(SELECT ID FROM BBS WHERE NAME='DB2');
答案完全正确。那么,现在让你查询一下DB2 的所有文章及评论,怎么办?传统的方法就很难查询了,这时候递归查询就派上用场了,如下:
SELECT
-- LEVEL 是一个伪列,表示当前行所属层次,从 1 开始
LEVEL,
-- CONNECT_BY_ISLEAF 表示当前行是否是叶子节点。1 表示叶子节点,0 表示非叶子节点。
CONNECT_BY_ISLEAF,
-- CONNECT_BY_ROOT 是一个操作符,用来修饰列,表示该列的值是根节点的值。
CONNECT_BY_ROOT NAME,
-- SYS_CONNECT_BY_PATH 是一个函数,返回层次路径
SYS_CONNECT_BY_PATH(NAME, '/')
FROM
BBS
-- START WITH 用来表示起始行
START WITH NAME = 'DB2'
-- CONNECT BY 用来指定父子连接条件
-- PRIOR 是一个操作符,用来修饰列,表示该列是父行中的列
CONNECT BY PRIOR ID = PARENT_ID
-- ORDER SIBLINGS BY 对同一层次中的行进行排序
ORDER SIBLINGS BY NAME
结果如下:
1 0 DB2 /DB2
2 0 DB2 /DB2/DB2 文章1
3 1 DB2 /DB2/DB2 文章1/DB2 文章1 的评论1
3 1 DB2 /DB2/DB2 文章1/DB2 文章1 的评论2
2 1 DB2 /DB2/DB2 文章2
上面的例子比较完美,假设由于bug,导致数据变成下面这样。
论坛首页
--数据库开发
----DB2
------DB2 文章1
--------DB2 文章1 的评论1
--------DB2 文章1 的评论2
----------DB2 文章1
此时,我们执行上面的语句,Oracle 会抛出错误。你肯定很想知道到底是哪个记录出错了,呵呵,该怎么办呢?试一试下面的语句吧。
SELECT
-- CONNECT_BY_ISCYCLE 是一个伪列,1 表示存在循环引用,否则为 0
CONNECT_BY_ISCYCLE,
NAME
FROM
BBS
-- NOCYCLE 指示 Oracle 出错不要停
CONNECT BY NOCYCLE PRIOR ID = PARENT_ID
其实递归查询还有好多其他用途,如: 你想生成从 1 到 100 的数字,怎么办?看看下面的 SQL 吧。
SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 100;
再如:你想生成从 1 到 100 的数字,但是要求它们用逗号分割。
SELECT
SYS_CONNECT_BY_PATH(R, ',')
FROM
(SELECT ROWNUM R FROM DUAL CONNECT BY ROWNUM <= 100)
WHERE
CONNECT_BY_ISLEAF = 1
START WITH R = 1
CONNECT BY R = PRIOR R + 1
当然,对于上面这个例子来说,上面的写法有点复杂,下面的写法更简单。
SELECT
LISTAGG(R, ',') WITHIN GROUP (ORDER BY R)
FROM
(SELECT ROWNUM R FROM DUAL CONNECT BY ROWNUM <= 100)
GROUP BY 1
--更多参见:Oracle SQL 精萃
-- 声明:转载请注明出处
-- Last edited on 2015-06-16
-- Created by ShangBo on 2015-06-16
-- End