mariadb 10.2/mysql 8.0实现递归
查询1-n的递归
mysql> WITH RECURSIVE cte (n) AS
( SELECT 1 /* seed query */
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5 /* recursive query */
)SELECT * FROM cte;
+---+
| n |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+
5 rows in set (0.00 sec)
我们先来看下WITH RECURSIVE子句:
cte是子查询的名称,(n)是列,子查询语句为(SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n < 5),其中SELECT 1是种子SELECT,只执行一次,而SELECT n+1 FROM cte WHERE n<5是递归SELECT,也就是说这个递归查询会一直执行,直到n的值不小于5为止,注意在递归SELECT中引用于自身cte。子查询定义好后,再用一个SELECT来查询这个cte即可。
借助mysql 8.0的cte(它是iso sql标准的一部分),可以实现递归,mariadb 10.2.2开始支持递归cte,如下:
+----+----------+--------------+
| ID | ParentID | name |
+----+----------+--------------+
| 1 | 0 | 河南省 |
| 2 | 1 | 信阳市 |
| 3 | 2 | 淮滨县 |
| 4 | 3 | 芦集乡 |
| 5 | 1 | 安阳市 |
| 6 | 5 | 滑县 |
| 7 | 6 | 老庙乡 |
| 8 | 2 | 固始县 |
| 9 | 8 | 李店乡 |
| 10 | 2 | 息县 |
| 11 | 10 | 关店乡 |
| 12 | 3 | 邓湾乡 |
| 13 | 3 | 台头乡 |
| 14 | 3 | 谷堆乡 |
| 15 | 1 | 南阳市 |
| 16 | 15 | 方城县 |
| 17 | 1 | 驻马店市 |
| 18 | 17 | 正阳县 |
+----+----------+--------------+
由下而上
sql:
with recursive r as
(
select * from c where id =11
union all
select c.* from c,r where c.id=r.ParentID
) select * from r order by id;
result:
+------+----------+-----------+
| ID | ParentID | name |
+------+----------+-----------+
| 1 | 0 | 河南省 |
| 2 | 1 | 信阳市 |
| 10 | 2 | 息县 |
| 11 | 10 | 关店乡 |
+------+----------+-----------+
由上而下
sql:
with recursive r as
(
select id,name from c where id=1
union all
select c.id,CONCAT(r.name, '>', c.name) as name from c,r where r.id = c.ParentID
)select id,name from r;
result:
+------+-----------------------------------------+
| id | name |
+------+-----------------------------------------+
| 1 | 河南省 |
| 2 | 河南省>信阳市 |
| 5 | 河南省>安阳市 |
| 15 | 河南省>南阳市 |
| 17 | 河南省>驻马店市 |
| 3 | 河南省>信阳市>淮滨县 |
| 6 | 河南省>安阳市>滑县 |
| 8 | 河南省>信阳市>固始县 |
| 10 | 河南省>信阳市>息县 |
| 16 | 河南省>南阳市>方城县 |
| 18 | 河南省>驻马店市>正阳县 |
| 4 | 河南省>信阳市>淮滨县>芦集乡 |
| 7 | 河南省>安阳市>滑县>老庙乡 |
| 9 | 河南省>信阳市>固始县>李店乡 |
| 11 | 河南省>信阳市>息县>关店乡 |
| 12 | 河南省>信阳市>淮滨县>邓湾乡 |
| 13 | 河南省>信阳市>淮滨县>台头乡 |
| 14 | 河南省>信阳市>淮滨县>谷堆乡 |
+------+-----------------------------------------+
完整原理大家可以参考https://mariadb.com/kb/en/library/recursive-common-table-expressions-overview/。
相关文章
- MySQL数据库设计规范
- 【数据库开发】is not allowed to connect to this MySQL server解决办法
- 大数据-数仓-离线数据分析-Hive(二):安装【元数据库用MySQL】【单节点,不需要集群】【启动脚本】
- 【dvwa时的奇奇怪怪的问题】(burpsuit的问题,dvwa的问题)(暴力破解、MySQL注入等)
- mysql索引之哈希索引
- MySQL中实现递归查询
- MySQL递归查询树状表的子节点、父节点具体实现
- MYSQL数据导出与导入,secure_file_priv参数设置
- MySQL绿色版安装(mysql-5.7.12-win32)
- MySQL递归查询函数
- Mysql:可恨又可怜Query Cache特性:已死!莫用!
- Mysql:skip-name-reslove
- mysql 递归查找菜单节点的所有子节点
- 你知道MySQL的LRU链表吗?
- mysql:常用的优化手段
- Centos7 安装mysql-8.0.13(rpm)
- Mysql中的递归层次查询(父子查询,无限极查询)
- MYSQL错误解决:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
- mysql中的视图、事务和索引
- mysql 存在update不存在insert