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()等来实现快速的筛选。
文章评论