本章节内容适用于版本号为3.1.4及以上的AnalyticDB for MySQL实例。通过使用递归查询,一个WITH查询可以引用它自己的输出,主要用于层次结构的查询。

语法

CTE(Common Table Expressions)即公用表表达式,公用表表达式是使用WITH子句定义的,WITH子句的使用请参见WITH。语法结构如下:
with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
参数说明:
  • cte_name:单个公用表表达式的名称,并可在包含WITH子句的语句中用作表来引用。
  • subquery:被称为“CTE的子查询”,它产生了CTE的结果集。必须要带括号。
如果CTE的子查询引用它自己的名称,则该表达式是递归的。例如:
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;
执行后,该语句将产生以下结果,即包含简单线性序列的单列:
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
递归CTE的结构
  • 如果WITH子句中的任何CTE是递归的,则必须包含RECURSIVE关键字。
  • 递归CTE子查询分为两部分,由UNION[ALL]或UNION DISTINCT分隔。
    SELECT ...      -- return initial row set
    UNION ALL
    SELECT ...      -- return additional row sets

    第一个SELECT生成CTE的初始行或多个行,并且不引用CTE名称。第二个SELECT通过引用其FROM子句中的CTE名称来生成其他行并递归。当此部分不生成新行时,递归结束。因此,递归CTE包含一个非递归的SELECT部分和一个递归的SELECT部分。 每个SELECT部分本身可以是多个SELECT语句的并集。

递归CTE子查询的语法约束

  • 递归SELECT中,不支持 agg 函数、window 函数、GROUP BY、ORDER BY、DISTINCT、LIMIT(与MySQL限制相同,MySQL 8.0.19后支持 LIMIT,ADB目前不支持)。
  • 不支持子查询中列和CTE中列定义不同,如下fibonacci数列中,CTE中第2、3列分别是fib_n、next_fib_n,是SELECT中的第3、2列,目前不支持。
    WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
    (
      SELECT 1, 0, 1
      UNION ALL
      SELECT n + 1, next_fib_n, fib_n + next_fib_n
        FROM fibonacci WHERE n < 10
    )
    SELECT * FROM fibonacci;
  • SELECT子查询中类型不同时,会cast为SELECT的非递归部分。

使用场景

  1. 日期序列生成
    公用表表达式可以生成一系列连续的日期,这对于生成摘要(包括该系列中所有日期的一行,包括未在汇总数据中表示的日期)非常有用。如下示例为生成日期范围序列,并计算出范围内每个日期的销售总和。
    WITH RECURSIVE dates (date) AS
    (
      SELECT MIN(date) FROM sales
      UNION ALL
      SELECT date + INTERVAL 1 DAY FROM dates
      WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
    )
    SELECT * FROM dates;WITH RECURSIVE dates (date) AS
    (
      SELECT MIN(date) FROM sales
      UNION ALL
      SELECT date + INTERVAL 1 DAY FROM dates
      WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
    )
    SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price
    FROM dates LEFT JOIN sales ON dates.date = sales.date
    GROUP BY dates.date
    ORDER BY dates.date;
  2. 分层数据遍历
    基于树状的组织架构,查询某个节点下的所有节点。
    WITH RECURSIVE employee_paths (id, name, path) AS
    (
      SELECT id, name, CAST(id AS CHAR(200))
        FROM employees
        WHERE manager_id IS NULL
      UNION ALL
      SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
        FROM employee_paths AS ep JOIN employees AS e
          ON ep.id = e.manager_id
    )
    SELECT * FROM employee_paths ORDER BY path;

限制公用表表达式递归

对于递归CTE,重要的是递归查询部分包括终止递归的条件。MAX_RECURSION_DEPTH 用来限制CTE的最大深度,默认值为10,超过默认值执行时会报错。可以通过hint来调整大小,考虑到执行效率,建议最好不要调整。如果调整,不能超过30,否则可能因为递归层数太多报错。

指定递归的最大层数为20,示例如下:
/*+MAX_RECURSION_DEPTH=20*/ SQL