4-1 非递归 CTE

MySQL8.0 开始支持通用表表达式(CTE)，即 WITH 子句。
派生表:
SELECT * FROM(SELECT 1) AS dt;
通用表表达式
WITH cte AS (SELECT 1) SELECT * FROM cte;

mysql8.0>select * from (select 1) as dt;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql8.0>SELECT * FROM (SELECT 1) AS dt;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql8.0>WITH cte AS (SELECT 1) SELECT * FROM cte;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql8.0>with cte1(id) as (select 1),
    -> cte2(id) as (select id+1 from cte1)
    -> select * from cte1 join cte2;
+----+----+
| id | id |
+----+----+
|  1 |  2 |
+----+----+
1 row in set (0.00 sec)

MySQL 8.0中的派生表（Derived Table）是一种使用子查询创建的临时表，通常用于连接操作或进行筛选、聚合等操作。派生表是在查询内部定义的，可以在查询的不同部分重复使用。

1. 派生表的用法举例：

下面是一个使用派生表的示例：

SELECT *  
FROM (  
  SELECT * FROM customers WHERE country = 'USA'  
) AS ct  
JOIN (  
  SELECT * FROM orders WHERE year = 2022  
) AS o  
ON ct.customer_id = o.customer_id;

在这个例子中，我们首先使用子查询从customers表中筛选出国家为’USA’的客户，并将其存储在一个名为ct的派生表中。然后，我们使用另一个子查询从orders表中筛选出年份为2022年的订单，并将其存储在一个名为o的派生表中。最后，我们将这两个派生表连接起来，基于customer_id列进行匹配。

2. 派生表的应用场景：

派生表通常用于以下场景：

• 复杂的连接操作：使用派生表可以将复杂的连接操作分解成多个步骤，从而提高查询的可读性和性能。
• 筛选和聚合：使用派生表可以对数据进行筛选和聚合操作，然后将其存储在临时表中，以便在后面的查询中重复使用。
• 数据重塑：派生表还可以用于对数据进行重塑，将数据转换成更适合后续查询的格式。

3. 派生表的注意事项：

使用派生表时需要注意以下几点：

• 派生表是在查询内部定义的临时表，只在查询期间存在。在查询执行完成后，派生表将被删除。
• 派生表可以在查询的不同部分重复使用，但每次引用时都需要重新计算。因此，在使用派生表时要注意避免过度计算临时结果集。
• 派生表可以嵌套在其他派生表中，但嵌套层数过多可能导致查询性能下降。因此，在使用派生表时要注意控制嵌套层数。

总之，派生表是一种有用的工具，可以提高查询的可读性和性能，特别是对于复杂的连接和筛选操作。在使用派生表时，需要注意其性能开销和嵌套层数，以避免对查询性能造成过大的影响。

4-2 递归 CTE

递归CTE在查询中引用自己的定义，使用 RECURSIVE 表示
WITH RECURSIVE cte (n) AS
(SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 5)
SELECT * FROM cte;

mysql8.0>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 |
+------+
5 rows in set (0.00 sec)

mysql8.0>

mysql8.0>select * from employees;
+------+---------+------------+
| id   | name    | manager_id |
+------+---------+------------+
|   29 | Pedro   |        198 |
|   72 | Pierre  |         29 |
|  123 | Adil    |        692 |
|  198 | John    |        333 |
|  333 | Yasmina |       NULL |
|  692 | Tarek   |        333 |
| 4610 | Sarah   |         29 |
+------+---------+------------+
7 rows in set (0.00 sec)

mysql8.0>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;
+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+
7 rows in set (0.32 sec)

4-3 递归限制

递归表达式的查询中需要包含一个终止递归的条件
cte_max_recursion_depth 最大递归查询深度
max_execution_time sql最大执行时间 单位是毫秒

mysql8.0>show variables like '%cte_max_recursion_depth%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| cte_max_recursion_depth | 1000  |
+-------------------------+-------+
1 row in set (1.98 sec)

mysql8.0>show variables like '%max_execution_time%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 0     |
+--------------------+-------+
1 row in set (0.00 sec)

mysql8.0>

当不限制递归查询深度时

mysql8.0> with recursive cte(n) as
    ->  (select 1 
    ->  union all
    ->  select n+1 from cte)
    -> select * from cte;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
mysql8.0>

4-4 CTE小结

• 通用表表达式与派生表类似，就像语句级别的临时表或视图。
• CTE可以在查询中多次引用，可以引用其他 CTE，可以递归。
• CTE支持 SELECT/INSERT/UPDATE/DELETE 等语句。

通用表表达式使用场景
MySQL通用表表达式（CTE）可以用于多种场景，下面是每个场景使用的语法格式：

1. 基本语法格式

WITH cte_name (column1, column2, ...) AS (
  SELECT ...
)
SELECT ...
FROM cte_name;

上述语法中，cte_name是临时表格的名称，可以根据需要进行命名。括号中的column1、column2等是可选的，用于指定临时表格中的列名。SELECT语句中的查询语句可以是任何有效的SELECT语句，用于定义临时表格中的数据。

2. 多表联合查询语法格式

WITH cte_name AS (
  SELECT ...
  UNION [ALL]
  SELECT ...
)
SELECT ...
FROM cte_name;

上述语法中，使用UNION或UNION ALL关键字定义多个SELECT语句，用于将多个表格合并。这种语法格式可以用于多表联合查询，以及数据的合并和处理。

3. 递归查询语法格式

WITH RECURSIVE cte_name (column1, column2, ...) AS (
  SELECT ...
  UNION [ALL]
  SELECT ...
)
SELECT ...
FROM cte_name;

上述语法中，使用WITH RECURSIVE关键字定义递归CTE，用于处理具有层次结构的数据。递归CTE必须包含两个SELECT语句，第一个SELECT语句用于初始化递归，第二个SELECT语句用于递归查询。在递归查询中，必须引用CTE本身。

4. 分组聚合语法格式

WITH cte_name AS (
  SELECT ...
  GROUP BY ...
)
SELECT ...
FROM cte_name;

上述语法中，使用GROUP BY关键字将查询结果按照指定的列进行分组。这种语法格式可以用于分组聚合查询，以及数据的统计和汇总。
注意：以上语法格式仅供参考，在实际使用时需要根据具体场景进行调整。

总之，CTE可以用于处理复杂的数据结构和逻辑，适用于多表联合查询、递归查询、分组聚合等场景。使用CTE可以提高查询语句的可读性和可维护性，减少代码的重复和冗余，同时也可以提高查询性能。

课后习题

使用递归 CTE 生成斐波那契数列: 0，1，1，2，3，5，8

mysql8.0>WITH RECURSIVE fibonacci (n, a, b) AS (
    ->   SELECT 0, 0, 1
    ->   UNION ALL
    ->   SELECT n+1, b, a+b FROM fibonacci WHERE n < 6
    -> )
    -> SELECT a FROM fibonacci;
+------+
| a    |
+------+
|    0 |
|    1 |
|    1 |
|    2 |
|    3 |
|    5 |
|    8 |
+------+
7 rows in set (0.00 sec)

递归查询包含三个列：n表示当前斐波那契数列的索引，a表示前一个斐波那契数，b表示当前斐波那契数。
查询的第一个部分定义了递归查询的起始条件，即第一个斐波那契数0和第二个斐波那契数1。查询的第二个部分是递归查询本身，它通过将前一个斐波那契数a与当前斐波那契数b相加来计算下一个斐波那契数，并在每次递归时增加索引n。递归查询会一直执行，直到n小于6，因为我们只需要生成前7个斐波那契数。
最后，查询从递归查询中选择前一个斐波那契数a作为结果集，以生成所需的斐波那契数列0，1，1，2，3，5，8。

mysql8.0>WITH RECURSIVE fib AS (
    ->     SELECT 0 AS f, 1 AS next_f
    ->     UNION ALL
    ->     SELECT next_f, f + next_f FROM fib WHERE f < 8
    -> )
    -> SELECT f FROM fib;
+------+
| f    |
+------+
|    0 |
|    1 |
|    1 |
|    2 |
|    3 |
|    5 |
|    8 |
+------+
7 rows in set (0.00 sec)