在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;
让我们逐个解释每个部分的含义:
- WITH cte_name (column1, column2, ...):这是公用表表达式的声明部分。cte_name是你给CTE起的别名,可以在后续查询中引用它。column1, column2, ...是可选的,用于指定列名,这样可以给CTE的结果集列指定名称。
- AS:这是CTE声明的分隔符。
- SELECT column1, column2, ... FROM your_table WHERE condition:这是CTE的查询部分,它类似于普通的SELECT查询,用于获取临时结果集。你可以在这里使用任何合法的SELECT查询语句。
- 后续查询:在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;
让我们逐个解释每个部分的含义:
- WITH RECURSIVE cte_name (non_recursive_part): 这是递归公用表表达式的声明部分。cte_name是你给RCTE起的别名,可以在后续查询中引用它。non_recursive_part是递归之前的部分查询,用于获取初始的结果集。
- AS: 这是RCTE声明的分隔符。
- SELECT ... FROM ... WHERE ...: 这是RCTE的非递归部分的查询,它类似于普通的SELECT查询,用于获取初始的结果集。
- UNION [ALL]: 这是连接非递归部分和递归部分查询结果的操作符。UNION用于去除递归中重复的行,UNION ALL保留重复的行。
- SELECT ... FROM cte_name WHERE ...: 这是RCTE的递归部分的查询。在这里,我们可以在CTE内部引用CTE本身,形成递归查询。
- 后续查询:在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;
————————————————
版权声明:本文为CSDN博主「WalkingWithTheWind~」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/a2272062968/article/details/131888595