WITH递归原理

公用表表达式(CTE):common table expression

CTE支持递归查询，定义一个递归CTE至少需要两个(可能更多)查询：第一个查询称为定位点成员，第二个查询称为递归成员，基本格式如下：

WITH <CTE_NAME>[(TARGET_COLUMN_LIST)]
AS
(
<ANCHOR_MEMBER>
UNION ALL
<RECURSIVE_)MEMEBER>
)
<OUTER_QUERY_AGAINST_CTE>;

定位点成员只是一个返回有效关系结果表的查询，与用于定义非递归表达式的查询类似，定位点成员查询只被调用一次。

下面举例理解递归原理：

以下代码演示了如何使用递归CTE来返回有关某个雇员及其所有各级下属的信息

WITH EMPSCTE AS
(
SELECT EMPID,MGRID, FIRSTNAME,LASTNAME
FROM HR.EMPLOYEES
WHERE EMPID=2
UNION ALL
SELECT C.EMPID,C.MGRID,C.FIRSTNAME,C.LASTNAME FROM
EMPSCTE AS P JOIN HR.EMPLOYEES AS C ON C.MGRID =P.EMPID
)
SELECT EMPID ,MGRID,FIRSTNAME,LASTNAME
FROM EMPSCTE;

定位点成员对HR.employees表进行查询，只返回雇员ID等于2的雇员行：

select empid ,mgrid,firstname,lastname 
from hr.employees 
where empid=2

递归成员查询对CTE(代表前一次递归调用的结果集)和employees表进行联接，返回在前一次递归调用的结果集中所返回雇员的直接下级：

SELECT C.EMPID,C.MGRID,C.FIRSTNAME,C.LASTNAME FROM
EMPSCTE AS P JOIN HR.EMPLOYEES AS C ON C.MGRID =P.EMPID

换句话说，要多次重复调用递归成员，每次调用返回下一级下属，第一次调用递归成员，返回雇员2的直接下属：雇员3和雇员5.第二次调用递归成员，返回雇员3和雇员5 的直接下属：雇员4，6，7，8，9.第三次调用递归成员，没有更多地下级雇员，递归成员返回一个空的结果集，递归至此结束。

WITH递归原理

推荐阅读