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