MySQL8.0的WITH查询详情
16lz
2021-12-09
关于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)
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)
这个是将临时表列名指定在第一行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
,递归的将父节点数据放入临时表
最终查询出来的就是递归的结果。
更多相关文章
- Android(安卓)SDK 和 杂7杂8
- Android(安卓)验证码输入框 连续性粘贴 使用第三方包
- Android之自定义Adapter的ListView
- ReactNative Android/iOS 打包详情流程
- android sdk 版本以及对应 API level
- android 跳转到当前应用的详情页面
- android中的自定义控件
- android平台搭建详情
- Android之自定义Adapter的ListView