sql递归查询-mysql8为例
总体描述
数据准表
通过CTE实现，当前版本是mariadb 8.0.31 ，于mysql8相当。
数据表（也就是原表）：
select * from recurrence a
| id | parent_id |
|---|---|
| 全球 | null |
| 中国 | 全球 |
| 美国 | 全球 |
| 辽宁省 | 中国 |
| 黑龙江省 | 中国 |
| 河南省 | 中国 |
| 郑州市 | 河南省 |
| 洛阳市 | 河南省 |
| 沈阳市 | 辽宁省 |
| 二七区 | 郑州市 |
| 某街道 | 二七区 |
| 某社区 | 某街道 |
| 某小区 | 某社区 |
| 十三栋 | 某小区 |
问题
想要查询每个等级的区域对应的全部路径。
代码：
with recursive t1 as(
select id, parent_id, cast(id as char(200) )as path
from recurrence a
where a.parent_id is null
union all
select b.id, b.parent_id, cast(concat(t1.path,'->',b.id) as char(200))as path
from t1 inner join recurrence b on b.parent_id=t1.id
)
select * from t1
;
结果：
| id | parent_id | path |
|---|---|---|
| 全球 | 全球 | |
| 中国 | 全球 | 全球->中国 |
| 美国 | 全球 | 全球->美国 |
| 辽宁省 | 中国 | 全球->中国->辽宁省 |
| 黑龙江省 | 中国 | 全球->中国->黑龙江省 |
| 河南省 | 中国 | 全球->中国->河南省 |
| 郑州市 | 河南省 | 全球->中国->河南省->郑州市 |
| 洛阳市 | 河南省 | 全球->中国->河南省->洛阳市 |
| 沈阳市 | 辽宁省 | 全球->中国->辽宁省->沈阳市 |
| 二七区 | 郑州市 | 全球->中国->河南省->郑州市->二七区 |
| 某街道 | 二七区 | 全球->中国->河南省->郑州市->二七区->某街道 |
| 某社区 | 某街道 | 全球->中国->河南省->郑州市->二七区->某街道->某社区 |
| 某小区 | 某社区 | 全球->中国->河南省->郑州市->二七区->某街道->某社区->某小区 |
| 十三栋 | 某小区 | 全球->中国->河南省->郑州市->二七区->某街道->某社区->某小区->十三栋 |
详解
在临时表中分为两个部分：
- 查询
全球, - 递归地查询剩余部分
以下分为详细步骤：
- 第一： 查询【全球】，此时只有一行。注意这一行数，就是当前t1的全部。
输入：
select id, parent_id, cast(id as char(200) )as path
from recurrence a
where a.parent_id is null
输出：
当前 t1
| id | parent_id | path |
|---|---|---|
| 全球 | null | 全球 |
- 第二： 查询原表与cte取交集。原表有14行，但是与当前cte有交集的，只有2行。
输入：
sql语句：
select b.id, b.parent_id, cast(concat(t1.path,'->',b.id) as char(200))as path
from t1 inner join recurrence b on b.parent_id=t1.id
表1 当前的 cte
| id | parent_id | path |
|---|---|---|
| 全球 | null | 全球 |
表2: 原表
输出：（也就是当前的t1）
| id | parent_id | path |
|---|---|---|
| 全球 | null | 全球 |
| 中国 | 全球 | 中国->全球 |
| 美国 | 全球 | 美国->全球 |
- 第三步：取 t1 表和原表取交集，注意当前的t1是三行， 原表是物理存储的表因此不变。于是拿到父母是 中国、美国的所行（一共三行），再并入到 t1中。
计算交集的结果：
| id | parent_id | path |
|---|---|---|
| 辽宁省 | 中国 | 全球->中国->辽宁省 |
| 黑龙江省 | 中国 | 全球->中国->黑龙江省 |
| 河南省 | 中国 | 全球->中国->河南省 |
并入到t1中（也就是现在的t1）
| id | parent_id | path |
|---|---|---|
| 全球 | 全球 | |
| 中国 | 全球 | 全球->中国 |
| 美国 | 全球 | 全球->美国 |
| 辽宁省 | 中国 | 全球->中国->辽宁省 |
| 黑龙江省 | 中国 | 全球->中国->黑龙江省 |
| 河南省 | 中国 | 全球->中国->河南省 |
- 以此类推，一直到 【十三栋】那一行，无法在原物理表中找到 parent_id ='十三栋' 的行，则停止。并且在临时表外输出所有数据。
| id | parent_id | path |
|---|---|---|
| 全球 | 全球 | |
| 中国 | 全球 | 全球->中国 |
| 美国 | 全球 | 全球->美国 |
| 辽宁省 | 中国 | 全球->中国->辽宁省 |
| 黑龙江省 | 中国 | 全球->中国->黑龙江省 |
| 河南省 | 中国 | 全球->中国->河南省 |
| 郑州市 | 河南省 | 全球->中国->河南省->郑州市 |
| 洛阳市 | 河南省 | 全球->中国->河南省->洛阳市 |
| 沈阳市 | 辽宁省 | 全球->中国->辽宁省->沈阳市 |
| 二七区 | 郑州市 | 全球->中国->河南省->郑州市->二七区 |
| 某街道 | 二七区 | 全球->中国->河南省->郑州市->二七区->某街道 |
| 某社区 | 某街道 | 全球->中国->河南省->郑州市->二七区->某街道->某社区 |
| 某小区 | 某社区 | 全球->中国->河南省->郑州市->二七区->某街道->某社区->某小区 |
| 十三栋 | 某小区 | 全球->中国->河南省->郑州市->二七区->某街道->某社区->某小区->十三栋 |
