MySQL8.0的WITH查询详情
作者:janbar 发布时间:2024-01-24 16:43:16
关于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
,递归的将父节点数据放入临时表
最终查询出来的就是递归的结果。
来源:https://www.cnblogs.com/janbar/p/15112099.html


猜你喜欢
- 由于 Ubuntu 中的汉字输入实在是太不友好了,所以装了个 搜狗输入法,好不容易把 搜狗输入法装好,本以为可以开开心心的搞代码了,然而。。
- function ReportFileStatus(filespec) { var fso, s = filespec; fso = new
- 如何用ADO批量更新记录?是的,ADO有这项功能,不过好像用的人不太多(不了解还是不会用呢?):<HTML> &nbs
- 增加索引可以提高查询效率。增加索引就是增加一个索引文件,存放的是数据的地址,类似与我们文档的目录,在查找过程中可以不用从书的内容查找,直接根
- 在本地windows机器开发的Django项目运行正常,放到服务器上后响应超慢,花了一整个工作日没找到原因(非常绝望),又花了一整个周末才找
- 一、定义函数在Python中可以使用def关键字来定义函数,命名规则跟变量的命名规则是一致的。在函数名后面的圆括号中可以放置传递给函数的参数
- 目录I. 前提II. 实例演示1. 硬编码方式2. 基于TransactionProxyFactoryBean方式3. xml使用方式4.
- 本文实例讲述了Python3爬虫学习之应对网站反爬虫机制的方法。分享给大家供大家参考,具体如下:如何应对网站的反爬虫机制在访问某些网站的时候
- 一、背景分析对想要在视觉化环境下制作复杂网页的专业网页制作者来说,Dreamweaver已经渐渐在网页编辑工具中展露头角,成为专业人士编写网
- pandas中iloc()函数DataFrame.iloc纯基于整数位置的索引。import pandas as pdmydict = [{
- 首先,来说一下对话框: 对话框在Windows应用程序中使用非常普遍,许多应用程序的设定,与用户交互需要通过对话框来进行,因此对话框是Win
- pandas模块pandas是一个强大的分析结构化数据的工具集;它的使用基础是Numpy(提供高性能的矩阵运算);用于数据挖掘和数据分析,同
- 和设计师打过交道的人一定也见到过少数极品,不是扎着小辫子留着小胡子,就是剃了光头抽根烟,通常说起来一套一套的人作品都很一般般,而作品一般般的
- CSS3草案中定义了{opacity:来声明元素的透明度,这已经得到了大多数现代浏览器的支持,而IE则很早通过特定的私有属性filter来实
- 下面是我写的NumericStepper:谢谢 果果 和 Rimifon , 我对代码进行了完善, 支持自适应小数位数:
- 南京的炎炎夏日也未能挡住书友会朋友们的参与,下午两点半我们正式开始了这个月的话题:“浏览器”。一开始大家大致上说了一些自己认为各浏览器中各自
- 我们在开发过程中,经常遇到这样问题,就是要求定期进行数据库的检查,如果发现特定数据,那么就要进行某项操作,这个需求呢,有两种做法,一是利用W
- Go语言没有继承、构造函数和析构函数等概念,但是它是面向对象的。.net中类型系统分为值类型和引用类型,两种转换需要进行装箱和拆箱,都是继承
- 如下所示:x = 3print(x+"nihao")这样会报错x = 3print(x,"nihao"
- 目录1、read_sql_query 读取 mysql2、to_sql 写入数据库 要实现 pandas 对 mysql 的读写需