CTE与derived table最大的不同之处是

• 可以自引用，递归使用（recursive cte
• 在语句级别生成独立的临时表. 多次调用只会执行一次
• 一个cte可以引用另外一个cte
• 一个CTE语句其实和CREATE [TEMPORARY] TABLE类似，但不需要显式的创建或删除，也不需要创建表的权限。更准确的说，CTE更像是一个临时的VIEW

语法：

一条语句里可以创建多个cte，用逗号隔开:

     cta2 AS (SELECT SUM(k) from sbtest2 WHERE id < 100) 
  SELECT * FROM cta1 JOIN cta2 ;
  +----------+----------+
  | sum(k)   | SUM(k)   |
  +----------+----------+
  | 49529621 | 49840812 |
  +----------+----------+
  1 row in set (0.00 sec)

递归CTE示例：

递归CTE需要加RECURSIVE关键字，使用Union all来产生结果

  SELECT ...定义初始化值，不引用自身, 同时初始化值的列也定义了cte上的列的个数和类型，可以用cast重定义
  SELECT ....返回更多的值，并定义退出循环条件，这里引用了cte自身
  其实现类似于：
  - if no rows, exit
  - (A): recursive query block is evaluated over the tmp table's lastly inserted
         rows, and it produces new rows which are appended to the tmp table (if UNION
             ALL; only distinct not-already-there rows if UNION DISTINCT)
  - if the last step didn't produce new rows, exit
- goto (A)

再举个典型的斐波拉契数(Fibonacci Series Generation)

  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;
  +------+-------+------------+
  | n    | fib_n | next_fib_n |
  +------+-------+------------+
  |    1 |     0 |          1 |
  |    2 |     1 |          1 |
  |    3 |     1 |          2 |
  |    4 |     2 |          3 |
  |    5 |     3 |          5 |
  |    6 |     5 |          8 |
  |    7 |     8 |         13 |
  |    8 |    13 |         21 |
  |    9 |    21 |         34 |
  |   10 |    34 |         55 |
  +------+-------+------------+

关于递归的深度，除了自定义推出条件外，为了避免无限递归，也定义了一个系统参数cte_max_recursion_depth来限制深度，默认值为1000:

前文已经说过，笔者对Server层代码了解不多，这里只做简单的记录

想看实现思路可以阅读如下两个worklog：

WL#3634: Recursive WITH (Common Table Expression)

A Definitive Guide To MySQL Recursive CTE

MySQL | Recursive CTE (Common Table Expressions)