mysql-递归列出表中所有项的所有父级和父级

whlutmcx 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(406)

我有一个表，它的父/子层次结构支持多个（理论上无限）嵌套级别：

|------|-------------------|-------------|
|  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）。我没有成功地将这个查询扩展到整个表，是否有某种方法可以重置表中每一行的计数器变量？
我已经看到许多答案建议使用固定数量的连接，但是接受可变数量级别的解决方案会更好。
在MySQL8中，由于递归查询（thank you@gmb），这是可能的，但由于我们仍然在MySQL5.7上运行，我很感兴趣的是，是否也有针对旧版本的解决方案。

sql mysql recursive-query hierarchical-data

来源：https://stackoverflow.com/questions/63130896/mysql-recursively-list-all-parents-and-ancestors-of-all-items-in-table

2条答案

按热度按时间

pcrecxhr1#

如果您运行的是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

db小提琴演示：

id | all_parents
-: | :----------
 1 | 0          
 2 | 0          
 3 | 0          
17 | 3,0        
31 | 17,3,0

赞(0）回复(0）举报 2021-07-29

hmmo2u0o2#

CREATE PROCEDURE make_csv_parent ()
BEGIN
    CREATE TABLE temp ( id INT PRIMARY KEY, parent_id INT, parents TEXT);
    INSERT INTO temp (id, parent_id, parents)
    SELECT id, parent_id, parent_id
    FROM menu
    WHERE parent_id = 0;
    WHILE ROW_COUNT() DO
        INSERT IGNORE INTO temp (id, parent_id, parents)
        SELECT menu.id, menu.parent_id, CONCAT(menu.parent_id, ',', temp.parents)
        FROM menu 
        JOIN temp ON menu.parent_id = temp.id;
    END WHILE;
    SELECT id, parents FROM temp;
    DROP TABLE temp;
END

小提琴

赞(0）回复(0）举报 2021-07-29

我来回答

mysql-递归列出表中所有项的所有父级和父级

2条答案

相关问题

热门标签

最新问答