因为不|

janbar

园龄:4年5个月 粉丝:29 关注:10

2021-08-07 15:47 阅读 883 评论 1 推荐

关于MySQL8的WITH查询学习

前言

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

示例

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

copy
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,查询临时表,因此查询出的结果就显而易见了

copy
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个不一样的查询,查询结果都一样。
值得注意的是临时表里面的多个查询列数量和类型必须一样,不然会报错。

copy
这个是将临时表列名指定在第一行 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;

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

copy
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 ]

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

练习

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

copy
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,递归的将父节点数据放入临时表
最终查询出来的就是递归的结果。

总结

通过阅读官方文档,我知道了WITH查询是为了避免出现嵌套的子查询,每个查询结果都可以是一个临时表,然后总查询可以用到所有临时表的数据。
然后就是递归查询,可以解决树形接口的情况,数据有父子层级的那种。

posted @   janbar  阅读(883)  评论(1编辑  收藏  举报
编辑推荐:
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
阅读排行:
· [翻译] 为什么 Tracebit 用 C# 开发
· 腾讯ima接入deepseek-r1,借用别人脑子用用成真了~
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· DeepSeek崛起:程序员“饭碗”被抢,还是职业进化新起点?
· RFID实践——.NET IoT程序读取高频RFID卡/标签

FAVOURITE

点击右上角即可分享
微信分享提示
*✧⁺˚⁺ପ(๑・ω・)੭ु⁾⁾ 好好学习天天向上
进入亮色模式
进入亮色模式

FAVOURITE