MySQL8.0的WITH查询详情

关于mysql8的with查询学习

前言：

对于逻辑复杂的sql，with可以大大减少临时表的数量，提升代码的可读性、可维护性
mysql 8.0终于开始支持with语句了，对于复杂查询，可以不用写那么多的临时表了。
可以查看官方文档

1、示例

官方第一个示例，可以看出该查询语句创建了cte1,cte2,cte3,cte4这4个临时表，后面的临时表依赖前面的临时表数据。
最后一行为最终查询结果，实际ct4因为ct3结果包含3行数据，但是使用max,min得到一行结果。

with cte1(txt) as (select "this "),
     cte2(txt) as (select concat(cte1.txt,"is a ") from cte1),
     cte3(txt) as (select "nice query" union
                   select "query that rocks" union
                   select "query"),
     cte4(txt) as (select concat(cte2.txt, cte3.txt) from cte2, cte3)
select max(txt), min(txt) from cte4;
 
+----------------------------+----------------------+
| max(txt)                   | min(txt)             |
+----------------------------+----------------------+
| this is a query that rocks | this is a nice query |
+----------------------------+----------------------+
1 row in set (0,00 sec)

官方第二个示例是递归的用法，根据阅读文档，我分析下面查询结果如下。
首先定义一个临时表my_cte分析select 1 as n，这个是决定临时表的列名为n，值为1
然后select 1+n from my_cte where n<10，这个是递归查询n<10,并将1+n作为结果填充临时表
最终使用select * from my_cte，查询临时表，因此查询出的结果就显而易见了

with recursive my_cte as
(
  select 1 as n
  union all
  select 1+n from my_cte where n<10
)
select * from my_cte;
 
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0,00 sec)

根据我的理解写了如下2个不一样的查询，查询结果都一样。
值得注意的是临时表里面的多个查询列数量和类型必须一样，不然会报错。

这个是将临时表列名指定在第一行
with recursive my_cte(a,b,c) as
(
  select 1,1,1
  union all
  select 1+a,2+b,3+c from my_cte where a<10
)
select * from my_cte;
 
这个第一行没有指定列名，然后列名由第一个查询返回结果确定
with recursive my_cte as
(
  select 1 as a,1 as b,1 as c
  union all
  select 1+a,2+b,3+c from my_cte where a<10
)
select * from my_cte;

根据官方文档，临时表的语法模板如下，是可以有很多行的查询共同组成。

with recursive cte_name [list of column names ] as
(
  select ...      <-- specifies initial set
  union all
  select ...      <-- specifies initial set
  union all
  ...
  select ...      <-- specifies how to derive new rows
  union all
  select ...      <-- specifies how to derive new rows
  ...
)
[, any number of other cte definitions ]

官方文档还列出了，使用临时表时可以增删改查新表，具体可以去阅读官方文档。

3、练习

关于递归的练习主要用于表里面包含父节点id之类的，详情可以参考下面的练习。
定义下面这样的表，存储每个区域（省、市、区）的id，名字及上级区域的pid

 
create table tb(id varchar(3), pid varchar(3), name varchar(64));
 
insert into tb values('002', 0, '浙江省');
insert into tb values('001', 0, '广东省');
insert into tb values('003', '002', '衢州市');
insert into tb values('004', '002', '杭州市');
insert into tb values('005', '002', '湖州市');
insert into tb values('006', '002', '嘉兴市');
insert into tb values('007', '002', '宁波市');
insert into tb values('008', '002', '绍兴市');
insert into tb values('009', '002', '台州市');
insert into tb values('010', '002', '温州市');
insert into tb values('011', '002', '丽水市');
insert into tb values('012', '002', '金华市');
insert into tb values('013', '002', '舟山市');
insert into tb values('014', '004', '上城区');
insert into tb values('015', '004', '下城区');
insert into tb values('016', '004', '拱墅区');
insert into tb values('017', '004', '余杭区');
insert into tb values('018', '011', '金东区');
insert into tb values('019', '001', '广州市');
insert into tb values('020', '001', '深圳市');
 
with recursive cte as (
 select id,name from tb where id='002'
 union all
 select k.id, concat(c.name,'->',k.name) as name from tb k inner join cte c on c.id = k.pid
) select * from cte;

执行结果：

分析结果包含第一行select id,name from tb where id='002'的数据，此时表中只有一行数据
然后连表查询select k.id, concat(c.name,'->',k.name) as name from tb k inner join cte c on c.id = k.pid，递归的将父节点数据放入临时表
最终查询出来的就是递归的结果。

到此这篇关于mysql的with查询详情的文章就介绍到这了,更多相关mysql的with查询内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！

MySQL8.0的WITH查询详情

目录

关于mysql8的with查询学习

1、示例

3、练习

相关推荐