ORACLE递归查询

2014-07-26

ORACLE支持常规的用CTE递归的方式实现递归查询，也有自己特有的查询方式，ORACLE文档中叫层次数据查询。这里通过一个简单的例子来介绍这两种查询方式。

数据准备

-- PID is parent ID of ID
CREATE TABLE TBL_TEST( ID NUMBER,  NAME VARCHAR2(100),  PID NUMBER);
/

BEGIN
    INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(1,'a',0);
    INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(2,'b',1);
    INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(3,'c',0);
    INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(4,'d',1);
    INSERT INTO TBL_TEST(ID,NAME,PID) VALUES(5,'e',2);
END;
 /

需求：显示ID = 1结点及下面所有的子结点（结果应为1、2、4、5）

ORACLE特有的递归查询方式 - CONNECT BY

SELECT * FROM TBL_Test
START WITH ID = 1 
CONNECT BY PRIOR ID = pid;

这种ORACLE特有的层次查询语法简洁而强大，更多关于ORACLE层次数据查询可参考官方文档

CTE方式

WITH cte(ID,NAME,PID) AS
(
    SELECT ID,NAME,PID
    FROM TBL_TEST
    WHERE ID = 1
UNION ALL
    SELECT b.ID,b.NAME,b.PID
    FROM cte a
    INNER JOIN TBL_TEST b ON a.id = b.PID
)
SELECT * FROM cte;

以上两种方法均查出了正确的结果。

ID|NAME|PID|
--+----+---+
 1|a   |  0|
 2|b   |  1|
 5|e   |  2|
 4|d   |  1|

上面第一种方法的Start With 相当于第二种方法的UNION ALL上面的部分，CONNECT BY 则相当于UNION ALL下面的部分。

CTE方法具有可移植性，SQL SERVER和DB2也支持。

关于CTE方法实现递归的更多实例，请看下面这篇文章：

SQL 递归查询实战