文章目录
- 非递归 CTE
- 1. 派生表(子查询)
- 2. CTE
- 3. CTE可以引用其他CTE
- 递归 CTE
- 1. 语法解释
- 2. 举个栗子
此学习文是基于MySQL 8.0写的
得益于大神朋友的悉心指导解决不少坑,才写出此文,向大神奉上膝盖
MySQL 8支持公用表表达式,包括非递归
和递归
两种。
公用表表达式允许使用命名的临时结果集,这是通过允许在SELECT
语句和某些其他语句前面使用WITH
子句来实现的。
不能在同一查询中两次引用派生表(子查询)
,因为那样的话,查询会根据派生表的引用次数计算两次或多次,这会引发严重的性能问题。使用CTE后,子查询只会计算一次。
非递归 CTE
1. 派生表(子查询)
select …… from ( subquery ) as derived, table_name ……;
2. CTE
with derived as (
subquery
)
select …… from derived, table_name ……;
CTE可能在SELECT/UPDATE/DELETE
之前,包括with derived as ( subquery )
的子查询, 例如:
with derived as (
subquery
)
delete from table_name
where table_name.col_name in (
select col_name from derived
);
3. CTE可以引用其他CTE
with derived_one as (
subquery
),
derived_two as (
select …… from derived_one
)
select …… from derived_one, derived_two ……;
递归 CTE
1. 语法解释
递归CTE是一种特殊的CTE,其子查询会引用自己的名字。WITH
子句必须以WITH RECURSIVE
开头。递归CTE子查询包括两部分:seed查询
和recursive查询
,由UNION[ALL]
或UNION DISTINCT
分隔。
-
seed select
被执行一次以创建初始数据子集; -
recursive select
被重复执行以返回数据的子集,直到获得完整的结果集。
当迭代不会生成任何新行时,递归会停止。这对挖掘层次结构
(父/子或部分/子部分)非常有用。
with recursive {temp_table_name}(n) as (
select …… from table_name /* "seed select" */
union all
select …… from {temp_table_name}, table_name /* "recursive select" */
)
select …… from derived ……;
假设要打印从 1 到 5 的所有数字:
mysql> with recursive derived(n) as (
-> select 1 /* "seed select" */
-> union all
-> select n + 1 from derived where n < 5 /* "recursive select" */
-> )
-> select * from derived;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)
在每次迭代中,SELECT
都会生成一个带有新值的行,比前一行的值 n 多 1。第一次迭代在初始行集合( 1 )上运行并生成值为 1 + 1 = 2 的行;第二次迭代对第一次迭代的行集合( 2 )进行操作并生成值为 2 + 1 = 3 的行。以此类推,一直持续到 n 不再小于 5 时,递归结束。
2. 举个栗子
假设要执行分层数据遍历,以便为每个员工生成一个组织结构图(即从CEO到每个员工的路径),也可以使用递归CTE。
- 创建带有
manager_id
的测试表
mysql> create table employees.employees_mgr (
-> id int primary key not null,
-> name varchar(100) not null,
-> manager_id int null,
-> index (manager_id),
-> foreign key (manager_id) references employees_mgr (id)
-> );
Query OK, 0 rows affected (0.03 sec)
- 插入示例数据
mysql> insert into employees.employees_mgr values
-> (333, "Yasmina", null), /* Yasmina is the CEO (manager_id is null) */
-> (198, "John", 333), /* John has id 198 and reports to 333 (Yasmina) */
-> (692, "Tarek", 333),
-> (29, "Pedro ", 198),
-> (4610, "Sarah", 29),
-> (72, "Pierre", 29),
-> (123, "Adil", 692);
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select id, name, manager_id from employees.employees_mgr;
+------+---------+------------+
| id | name | manager_id |
+------+---------+------------+
| 29 | Pedro | 198 |
| 72 | Pierre | 29 |
| 123 | Adil | 692 |
| 198 | John | 333 |
| 333 | Yasmina | NULL |
| 692 | Tarek | 333 |
| 4610 | Sarah | 29 |
+------+---------+------------+
7 rows in set (0.00 sec)
- 执行递归CTE
mysql> with recursive employee_paths (id, name, path) as (
-> select id, name, cast(id as char(200))
-> from employees.employees_mgr
-> where manager_id is null
-> union all
-> select , , concat(, '->', )
-> from employee_paths as ep
-> join employees.employees_mgr as e
-> on = e.manager_id
-> )
-> select * from employee_paths;
+------+---------+--------------------+
| id | name | path |
+------+---------+--------------------+
| 333 | Yasmina | 333 |
| 198 | John | 333->198 |
| 692 | Tarek | 333->692 |
| 29 | Pedro | 333->198->29 |
| 123 | Adil | 333->692->123 |
| 72 | Pierre | 333->198->29->72 |
| 4610 | Sarah | 333->198->29->4610 |
+------+---------+--------------------+
7 rows in set (0.00 sec)
with recursive employee_paths (id, name, path) as
是CTE的名称,列是(id, name, path)
。
select id, name, cast(id as char(200)) from employees.employees_mgr where manager_id is null
是查询CEO的seed查询
( 没有在CEO之上的管理者)。
select , , concat(, '->', ) from employee_paths as ep join employees.employees_mgr as e on = e.manager_id
是递归查询。
递归查询生成的每一行,会查找直接向前一行生成的员工做汇报的所有员工。对于每个员工,该行的信息包括员工ID、 姓名和员工管理链,该链是在最后添加了员工ID的管理链。