MySQL8.0的WITH查询实现递归遍历省市区

作者:admin 发布时间:2023-09-21 15:59:37 浏览:475次

WITH 子句是 MySQL 中的一种 SQL 结构,又称为 Common Table Expression (CTE)。它在不影响原有 SQL 语句的情况下,允许开发人员临时创建一个内存中的结果集,然后对其进行操作。


WITH 子句的主要用途是创建一个暂时的结果集,这个结果集在后续的查询中可以多次使用。WITH 子句主要用于解决查询复杂度高的问题,因为它可以将多次需要的计算结果集存储下来,以便后续的查询可以直接使用。它还可以帮助我们更好地组织复杂的 SQL 查询,使得代码更加清晰易读。


可以看出该查询语句创建了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 ]
官方文档还列出了,使用临时表时可以增删改查新表,具体可以去阅读官方文档。


递归示例

关于递归的练习主要用于表里面包含父节点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.zhe94.com/949.html

上一篇:mysql 复制表和表数据的方法
下一篇:Mysql语法批量提取json数组数据
MySQL-窗口函数有哪些

MySQL-窗口函数有哪些

定义 窗口可以理解为记录集合,窗口函数就是在满足某种条件的记录集合上执行的特殊函数。 即:应用在窗口内的函数。
MySQL查询连续n天登录的用户的方法

MySQL查询连续n天登录的用户的方法

这篇文章介绍了SQL查询至少连续n天登录用户的方法,文中通过示例代码介绍的非常详细。对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下
MySql中group_concat字符长度限制1024怎么办

MySql中group_concat字符长度限制1024怎么办

group_concat长度限制默认是1024,如果在实际使用过程中拼接好的字符串长度超过了最大长度,那么最后仅保留前1024个字符,解决group_concat长度限制有两个解决方法
mysql 怎么重启,Windows系统mysql8.0重启的方法详解

mysql 怎么重启,Windows系统mysql8.0重启的方法详解

Windows要重新启动 MySQL,你可以按照以下步骤进行操作,命令提示符界面中,输入以下命令来关闭 MySQL 服务
MySQL8.0的WITH查询实现递归遍历省市区

MySQL8.0的WITH查询实现递归遍历省市区

WITH 子句是 MySQL 中的一种 SQL 结构,又称为 Common Table Expression (CTE)。它在不影响原有 SQL 语句的情况下,允许开发人员临时创建一个内存中的结果集,然后对其进行操作。