共用表表达式(CTE)——阅读《MySQL8 Cookbook》笔记

公用表表达式(CTE)

MySQL8支持公用表达式，包括非递归和递归两种

非递归CTE

派生表：

SELECT…FROM (subquery) AS DERIVED, t1…

CTE

SELECT…WITH derived AS (subquery) SELECT … FROM derived, t1 …

CTE 可能在SELECT/UPDATE/DELETE之前，包括WITH derived AS (subquery)的子查询。

例如：了解每年工资较前一年的增长百分比

1. 使用两次子查询实现

   SELECT 
   	q1.year,
   	q2.year AS next_year,
   	q1.sum,
   	q2.sum AS next_sum,
   	100*(q2.sum-q1.sum)/q1.sum AS pct
   FROM
   	(SELECT year(from_date) as year, sum(salary) as sum FROM salaries GROUP BY year) AS q1,
   	(SELECT year(from_date) as year, sum(salary) as sum FROM salaries GROUP BY year) AS q2
   WHERE q1.year = q2.year-1;
   

   结果：

   

2. 使用CTE实现

   WITH CTE AS
   	(SELECT year(from_date) AS year, SUM(salary) AS sum FROM salaries GROUP BY year)
   SELECT
   	q1.year, q2.year as next_year, q1.sum, q2.sum as next_sum, 100 * (q2.sum-q1.sum)/q1.sum as pct FROM
   	CTE AS q1,
   	CTE AS q2
   WHERE
   	q1.year = q2.year-1;
   

   结果：

   

   可以看到，使用CTE后，结果与之前相同，但查询时间缩短了50%，可读性变好，而且可以被多次引用。

递归CTE

递归CTE是一种特殊的CTE，其子查询会引用自己的名字。WITH子句必须以WITH RECURSIVE开头，递归CTE子查询包括两部分：seed查询和recursive查询,由UNION[ALL]或UNION DISTINCT分隔。

1. seed查询

   SELECT被执行一次以创建初始数据子集

2. recursive查询

   SELECT被重复执行以返回数据的子集，直到获取完整的结果集

当迭代不会产生任何新行时，递归会停止

格式

WITH RECURSIVE cte AS

(SELECT … FROM table_name /* seed 查询 */

UNION ALL

SELECT … FROM cte, table_name) /* recursive 查询 */

SELECT … FROM cte;

例子

1. 打印1到5的所有数字

   WITH RECURSIVE cte(n) AS
   (SELECT 1 /*seed 查询*/
   UNION ALL
   SELECT n + 1 FROM cte WHERE n < 5 /*recursive 查询*/
   )
   SELECT * FROM cte;
   

2. 分层次数据遍历，为每个员工生成一个组织结构图

   WITH RECURSIVE employee_paths (id, name, path) AS
   (
   	SELECT id, name, CAST(id AS CHAR(200))
       FROM employees_mgr
       WHERE manager_id IS NULL
       UNION ALL
       SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
       FROM employee_paths AS ep JOIN employees_mgr AS e
       ON ep.id = e.manager_id
   )
   SELECT * FROM employee_paths ORDER BY path;
   
   -- 其中3-4行为seed查询，7-9行为recursive查询