简介
递归公用表达式
(CTE)是一个CTE,它有一个子查询,它引用CTE名称本身。
语法:
WITH RECURSIVE cte_name AS (
initial_query -- anchor member
UNION ALL
recursive_query -- 引用CTE名称的递归成员
SELECT * FROM cte_name;
递归CTE由三个主要部分组成:
- 初始查询,形成CTE结构的基本结果集。初始查询部分称为锚成员。
- 递归查询部分是引用CTE名称的查询,因此,它被称为递归成员。递归成员由UNION ALL或UNION DISTINCT运算符与锚成员连接。
- 终止条件,确保递归成员不返回任何行时停止递归。
递归CTE的执行顺序如下:
4. 首先,将成员分为两部分:锚点和递归成员。
5. 接下来,执行锚成员以形成基本结果集(R0),并将此基本结果集用于下一次迭代。
6. 然后,执行带有Ri结果集作为输入的递归成员并将其Ri+1作为输出。
7. 之后,重复第三步,直到递归成员返回空结果集,换句话说,满足终止条件。
8. 最后,使用UNION ALL运算符将结果集从R0到Rn组合。
递归成员限制:
递归成员不得包含一下结构:
9. 聚合函数,例如MAX,MIN,SUM,AVG,COUNT等。
10. GROUP BY子句
11. ORDER BY子句
12. LIMIT 子句
13. DISTINCT
注意:上述约束不适用于锚点成员。此外,禁止DISTINCT仅在您使用UNION时适用。如果您使用UNION DISTINCT,DISTINCT则允许。此外,递归成员只能在其FROM子句中引用CTE名称一次,而不能在任何子查询中引用。
MySQL递归CTE实例
WITH RECURSIVE cte_count (n)
AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM cte_count
WHERE n < 3
)
SELECT n
FROM cte_count;
在此实例中:
SELECT 1
是返回1作为基本结果集的锚成员。
以下查询:
SELECT n + 1
FROM cte_count
WHERE n < 3
是递归成员,因为它引用了CTE的名称cte_count。
n < 3 递归成员中的表达式是终止条件。一旦n等于3,递归成员将返回一个空集,该集将停止递归。
下图说明了上述CTE的要素:
递归CTE返回以下输出:
MySQL使用递归CTE遍历分层数据
使用mysqldemo示例数据库中的employees表进行演示。
employees表有reportsTo引用employeeNumber字段的字段。reportsTo列存储管理员的ID。最高管理者不会向公司组织结构中的任何人报告,因此,reportsTo列中的值为NULL。
可以应用递归CTE以自上而下的方式查询整个组织结构,如下所示:
WITH RECURSIVE employee_paths AS
( SELECT employeeNumber,
reportsTo managerNumber,
officeCode,
1 lvl
FROM employees
WHERE reportsTo IS NULL
UNION ALL
SELECT e.employeeNumber,
e.reportsTo,
e.officeCode,
lvl+1
FROM employees e
INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
managerNumber,
lvl,
city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;
首先,使用以下查询形成锚点成员:
SELECT
employeeNumber, reportsTo managerNumber, officeCode
FROM
employees
WHERE
reportsTo IS NULL
这个查询(锚定件)返回高级经理,其reportsTo是NULL。
其次,通过引用CTE名称来创建递归成员,employee_paths在这种情况下:
SELECT
e.employeeNumber, e.reportsTo, e.officeCode
FROM
employees e
INNER JOIN
employee_paths ep ON ep.employeeNumber = e.reportsTo
此查询(递归成员)返回管理器的所有直接汇报,直到没有更多的直接汇报。如果递归成员没有返回直接报告,则递归停止。
第三,使用employee_pathsCTE 的查询将CTE返回的结果集与offices表连接,以生成最终结果集。
以下是查询的输出: