SQL中的递归

使用通用公共表表达式CTE,(Common Table Expression)，可适用Oracle，DB2

包含三个组成部分：

初始查询：对基本表进行查询的部分。CTE 定义中的第一个 FULLSELECT 必须不包含对 CTE 自身的应用，即必须是初始查询

递归查询：通过对 CTE 自身的引用，从而启动递归逻辑的查询。遵循以下几个规则 ：

递归查询和初始查询结果必须包含相同数量的数据列；
递归查询和初始查询结果数据列的、长度等必须一致；
递归查询不能包含 GROUP BY 或者 HAVING 子句；
递归查询不能包含 Outer Join；
递归查询不能包含子查询 (Subquery)；
递归查询必须用 UNION ALL 联结。

终止条件
终止条件通常是隐性的，即如果前一次递归查询返回的结果集为空，则终止递归；但是也可以在递归查询中设定终止条件，如限定递归查询的深度等。  

WITH temptab(deptid, empcount, superdept) AS
   (    SELECT root.deptid, root.empcount, root.superdept
            FROM departments root
            WHERE deptname='Production'
     UNION ALL
        SELECT sub.deptid, sub.empcount, sub.superdept
            FROM departments sub, temptab super
            WHERE sub.superdept = super.deptid
   )
select * from temptab;

       

例子

CREATE TABLE emp(empid  INTEGER NOT NULL PRIMARY KEY, 2                    name   VARCHAR(10), 3                    salary DECIMAL(9, 2), 4                    mgrid  INTEGER);

谁是‘Goyal’的属下

1 WITH n(empid, name) AS 2           (SELECT empid, name 3              FROM emp4              WHERE name = 'Goyal'5            UNION ALL6            SELECT nplus1.empid, nplus1.name 7              FROM emp as nplus1, n8              WHERE n.empid = nplus1.mgrid)9 SELECT name FROM n;

例子

with n(tag_category_id,parent_tag_category_id) as

             (select tag_category_id 

               from ts_tag_category where parent_tag_category_id = ?

             union all

              select t.tag_category_id

               from ts_tag_category t left join n

               on n.tag_category_id = t.parent_tag_category_id)

select tag_category_id from n;

例子

【数据结构】

process_id par_process_id fw_id
742482939    414358129      471
414358129    1707251033     470
1707251033    null          468

【递归查询父process_id】
with par (process_id,par_process_id) as
(
    select process_id,par_process_id from docm_process_ts a where fw_id='471'【1】
union all
   select child.process_id,child.par_process_id from par ,docm_process_ts child where par.par_process_id=child.process_id 【2】
)
select process_id,par_process_id from par where par.par_process_id is null 【3】

【1】这条SQL语句是初始化,只执行一次,执行结果存在虚表中作为种子传递给【2】

【2】递归从虚表par 跟 docm_process_ts 表进行条件查出数据

【3】这条SQL语句是最终要查询的结果

           

Bauer 经理的所有下属

STAFFLIST 与表 STAFF 联合在一起。递归的层数的值都会增加 1，级别值都会增加 1。此外，应指定 SEARCH DEPTH FIRST 子句。临时创建的字段 SORT 用于 ORDER BY 子句内，以便按深度优先顺序获得返回的结果。

图 4：示例 RCTE – 确定所有下属