mysql - MySQL - 递归列出表中所有项目的所有父母和祖先
问题描述
我有一个支持多个(理论上无限)嵌套级别的父/子层次结构的表:
|------|-------------------|-------------|
| id | title | parent_id |
|------|-------------------|-------------|
| 1 | Dashboard | 0 |
| 2 | Content | 0 |
| 3 | Modules | 0 |
| 17 | User Modules | 3 |
| 31 | Categories | 17 |
| ... | | |
|------|-------------------|-------------|
我正在尝试构建一个查询,该查询生成每个项目的父项的串联列表,直到树中最高的父项:
|------|----------------------|
| id | concatenatedParents |
|------|----------------------|
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 17 | 3,0 |
| 31 | 17,3,0 |
| ... | |
|------|----------------------|
基于此处的许多其他答案,我构建了以下 MySQL 查询:
SELECT parentsTable._id, GROUP_CONCAT(parentsTable.parent_id SEPARATOR ',') as concatenatedParents FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM menu WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 31, @l := 0) vars,
menu m
WHERE @r <> 0
) as parentsTable
在此处查看小提琴:http ://sqlfiddle.com/#!9/48d276f/902/0
但此查询仅适用于一个给定的子 id(本例中为 31)。我没有成功为整个表扩展这个查询,有没有办法在表中的每一行重置计数器变量?
我已经看到许多建议使用固定数量的连接的答案,但是接受可变数量级别的解决方案会更可取。
在 MySQL 8 中,这可能要归功于递归查询(谢谢@GMB),但由于我们仍在 MySQL 5.7 上运行,我很感兴趣是否也存在适用于旧版本的解决方案。
解决方案
如果您运行的是 MySQL 8.0,最好使用递归查询来解决:
with recursive cte as (
select id, parent_id, 1 lvl from mytable
union all
select c.id, t.parent_id, lvl + 1
from cte c
inner join mytable t on t.id = c.parent_id
)
select id, group_concat(parent_id order by lvl) all_parents
from cte
group by id
编号 | all_parents -: | :---------- 1 | 0 2 | 0 3 | 0 17 | 3,0 31 | 17,3,0
推荐阅读
- google-cloud-dataflow - Apache Beam:刷新我正在使用 MongoDbIO.read() 从 MongoDB 读取的侧输入
- apache-flink - Flink Collector.collect(T) 耗时超过 150 秒
- python - 如何替换多索引的某些值?
- angular - PrimeNG - 在时间选择器组件中设置值
- c# - 如何阻止docfx在c#xml注释中将“\\”转义为“\”?
- pyqt - 从 pyqt5 文件到另一个 .py 文件的变量
- php - Laravel 忽略验证错误并使表单消失
- python - 如何摆脱\n?
- html - 如何只允许使用纯 HTML 的文本框上的字母数字
- javascript - Discord.js - Erela.js - Lavalink | 检查 Lavalink 是否没有返回结果