CTE(Common Table Expression) 介绍
CTE(公用表表达式)是一个临时结果集,可以在后续的单个SQL语句(例如SELECT, INSERT, UPDATE, DELETE )执行。
一个CTE返回的是一个结果集。也可以再查询中被多次引用。对于应对复杂的查询来说,这是最方便的方式。让复杂的查询结果变得简洁易懂。
在MySQL8.0版本开始支持MySQL的CTE。
CTE示例一
WITH
cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
CTE示例二
WITH Employee_CTE (EmployeeNumber, Title)
AS
(SELECT NationalIDNumber,
JobTitle
FROM HumanResources.Employee)
SELECT EmployeeNumber,
Title
FROM Employee_CTE
蓝色的部分就是一个CTE, 括号中的内容实际上就是一个查询,可以单独的运行这个查询。
黄色的部分就是如何使用这个CTE, 紧跟着一个SELECT语句查询这个CTE。
WITH Employee_CTE (EmployeeNumber, Title) 这里的EmployeeNumber 实际上是NationalIDNumber, Title就是JobTitle, 相当于别名。
WITH Employee_CTE 也可以不写后面的(),这样就是默认值,显示出来的就是NationalIDNumber,JobTitle。
递归CTE
递归表达式是CTE的子查询可以引用其本身,递归CTE子句中必须包含两个部分
- 定点(Anchor)子查询:定点查询只是一个返回有效表的查询,用于设置递归的初始值;
- 递归子查询:该子查询调用CTE名称,触发递归查询;
这两个子查询可以通过 UNION、UNION ALL或UNION DISTINCT 连接在一起。
注意
- WITH子句中要使用WITH RECURSIVE代替
定点子查询的SELECT只会执行一次,并得到初始的数据子集。
-
递归查询的SELECT是会重复执行直到没有新的行产生为止,为了避免无限循环,有时候需要加上结束条件。
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 |
+------+
CTE的好处
- 可读性 - 通常我们在创建查询时,会有很多临时的查询需求,最后把临时表拼接为一个结果集。这样我们的查询会变得很长,可读性很差。但是使用CTE可以避免这样的情况,每个CTE作为一个临时表,最终合并成一个Select最终的结果表。这样后续的代码维护也会变得简单。
- 简洁 - 可以在一个语句中多次引用公用表表达式(CTE)
- 递归 - 可以使用CTE来实现递归查询,后续会有实际的案例来介绍CTE的递归实际使用场景,比如查找所属省市区,工作中的组织架构等,存在上下级关系时。
- 排序 -可以搭配排序功能如ROW_NUMBER(), RANK(), NTILE()等来实现快速的筛选。
文章评论