MySQL - MySQL 8.0(三)进阶操作:WITH AS.公用表表达式(CTE)

此学习文是基于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 e.id, e.name, concat(ep.path, '->', e.id)
    -> from employee_paths as ep
    -> join employees.employees_mgr as e
    -> on ep.id = 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 e.id, e.name, concat(ep.path, '->', e.id) from employee_paths as ep join employees.employees_mgr as e on ep.id = e.manager_id是递归查询。
  递归查询生成的每一行,会查找直接向前一行生成的员工做汇报的所有员工。对于每个员工,该行的信息包括员工ID、 姓名和员工管理链,该链是在最后添加了员工ID的管理链。

  • 4
    点赞
  • 30
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值