公用表表达式(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.第三次调用递归成员,没有更多地下级雇员,递归成员返回一个空的结果集,递归至此结束。