Recursive CTE

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的结果集。必须要带括号。

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM 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语句的并集。

• 递归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;

/*+MAX_RECURSION_DEPTH=20*/ SQL