Mysql CTE(公用表表达式)介绍与用法

2020年05月19日 198点热度 0人点赞 0条评论

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

Y4eQj1.png

蓝色的部分就是一个CTE, 括号中的内容实际上就是一个查询,可以单独的运行这个查询。

黄色的部分就是如何使用这个CTE, 紧跟着一个SELECT语句查询这个CTE。

Y4ea3d.png

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

KOgoal

文章评论