MySQL8 新特性——公用表表达式用法

MySQL8 新特性——公用表表达式用法

在MySQL 8.0及更高版本中，引入了公用表表达式（Common Table Expressions，CTEs），它是一种方便且可重用的临时结果集，类似于子查询，但使用更简洁和易读的语法。CTEs通常用于在复杂的查询中创建临时表，以便于后续查询和分析。

普通公用表表达式

CTEs的语法结构如下：

WITH cte_name (column1, column2, ...) AS (
  -- CTE查询部分
  SELECT column1, column2, ...
  FROM your_table
  WHERE condition
)
-- 下面的查询使用 CTE
SELECT *
FROM cte_name;

让我们逐个解释每个部分的含义：

1. WITH cte_name (column1, column2, ...)：这是公用表表达式的声明部分。cte_name是你给CTE起的别名，可以在后续查询中引用它。column1, column2, ...是可选的，用于指定列名，这样可以给CTE的结果集列指定名称。

2. AS：这是CTE声明的分隔符。

3. SELECT column1, column2, ... FROM your_table WHERE condition：这是CTE的查询部分，它类似于普通的SELECT查询，用于获取临时结果集。你可以在这里使用任何合法的SELECT查询语句。

4. 后续查询：在CTE声明之后，你可以在同一SQL语句中使用该CTE，就像使用任何其他表一样。可以在SELECT、INSERT、UPDATE或DELETE语句中引用CTE，从而简化复杂的查询。

以下是一个示例，展示如何在MySQL中使用CTEs：

假设有一个名为 employees 的表，其中包含员工信息，包括列 employee_id、first_name、last_name和salary。现在，我们想要获取薪水高于平均薪水的员工列表：

WITH average_salary AS (
  SELECT AVG(salary) AS avg_salary
  FROM employees
)
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (SELECT avg_salary FROM average_salary);

在上面的查询中，我们首先使用CTE average_salary 计算员工薪水的平均值。然后，我们在后续查询中使用了这个CTE，通过比较员工的薪水与平均薪水来获取符合条件的员工列表。

通过使用公用表表达式，我们可以在查询中更清晰地组织和重用临时结果集，从而使查询更易读和维护。

递归公用表表达式

在MySQL 8.0及更高版本中，还引入了递归公用表表达式（Recursive Common Table Expressions，RCTEs），这使得在查询中能够处理递归数据结构变得更加方便。RCTEs允许在公用表表达式内部引用自身，从而创建递归查询，用于处理递归关系的数据。

RCTEs的语法结构如下：

WITH RECURSIVE cte_name (non_recursive_part) AS (
  -- 非递归部分的查询
  SELECT ...
  FROM ...
  WHERE ...
  
  UNION [ALL]
  
  -- 递归部分的查询
  SELECT ...
  FROM cte_name
  WHERE ...
)
-- 在此处可以继续查询
SELECT ...
FROM cte_name;

让我们逐个解释每个部分的含义：

1. WITH RECURSIVE cte_name (non_recursive_part): 这是递归公用表表达式的声明部分。cte_name是你给RCTE起的别名，可以在后续查询中引用它。non_recursive_part是递归之前的部分查询，用于获取初始的结果集。

2. AS: 这是RCTE声明的分隔符。

3. SELECT ... FROM ... WHERE ...: 这是RCTE的非递归部分的查询，它类似于普通的SELECT查询，用于获取初始的结果集。

4. UNION [ALL]: 这是连接非递归部分和递归部分查询结果的操作符。UNION用于去除递归中重复的行，UNION ALL保留重复的行。

5. SELECT ... FROM cte_name WHERE ...: 这是RCTE的递归部分的查询。在这里，我们可以在CTE内部引用CTE本身，形成递归查询。

6. 后续查询：在RCTE声明之后，你可以在同一SQL语句中使用该RCTE，就像使用任何其他表一样。可以在SELECT、INSERT、UPDATE或DELETE语句中引用RCTE，继续进行递归或其他操作。

下面是一个简单的示例，展示如何在MySQL中使用递归公用表表达式：

假设有一个名为 categories 的表，其中包含商品分类信息，包括列 category_id 和 parent_category_id。现在，我们想要通过递归查询，获取所有子分类的层级结构：

WITH RECURSIVE recursive_cte (category_id, parent_category_id, category_path) AS (
  -- 非递归部分：获取初始结果集
  SELECT category_id, parent_category_id, CAST(category_id AS CHAR) AS category_path
  FROM categories
  WHERE parent_category_id IS NULL
  
  UNION ALL
  
  -- 递归部分：连接上一级和当前级别的结果
  SELECT c.category_id, c.parent_category_id, CONCAT(rc.category_path, ',', c.category_id)
  FROM categories c
  INNER JOIN recursive_cte rc ON c.parent_category_id = rc.category_id
)
-- 最终查询：获取所有子分类的层级结构
SELECT * FROM recursive_cte;

在上面的查询中，我们使用了递归公用表表达式 recursive_cte。在非递归部分，我们获取初始结果集，即所有顶级分类（parent_category_id IS NULL）。然后，在递归部分，我们连接上一级和当前级别的结果，从而构建子分类的层级结构。我们使用 UNION ALL 来保留重复的行，以继续进行递归。最终查询结果包含了所有子分类的层级结构信息。

通过使用递归公用表表达式，我们可以轻松地处理具有递归结构的数据，并进行复杂的递归查询和分析。

# 查询所有员工的层级 n 1,2,3,4级别
WITH RECURSIVE cte AS (SELECT employee_id, last_name, manager_id, 1 AS n
                       FROM employees
                       WHERE employee_id = 100 -- 种子查询，找到第一代领导
                       UNION ALL
                       SELECT a.employee_id, a.last_name, a.manager_id, n + 1
                       FROM employees AS a
                                JOIN cte ON (a.manager_id = cte.employee_id) -- 递归查询，找出以递归公用表表达式的人为领导的人
)
SELECT *
FROM cte;