获取一个 SQL 查询中的所有父行

Question

我有一个简单的 MySQL 表，其中包含类别列表，级别由parent_id 确定：

id  name    parent_id
---------------------------
1   Home        0
2   About       1
3   Contact     1
4   Legal       2
5   Privacy     4
6   Products    1
7   Support     1

我正在尝试创建面包屑路径。所以我有孩子的“id”，我想获得所有可用的父母（迭代链直到我们到达 0“Home”）。可以有任何数量或子行，深度不受限制。

目前我正在为每个父级使用 SQL 调用，这很混乱。 SQL 有没有一种方法可以在一个查询中完成这一切？

Answer 1

改编自此处< /a>：

SELECT T2.id, T2.name
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 5, @l := 0) vars,
        table1 h
    WHERE @r <> 0) T1
JOIN table1 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

@r := 5 行是当前页的页码。结果如下：

1, 'Home'
2, 'About'
4, 'Legal'
5, 'Privacy'

Answer 2

接受的答案具有递归检索子用户的所有父用户的最佳解决方案。我已根据我的需要对此进行了修改。

对于 MySQL 5.5、5.6 和5.7

SELECT @r AS user_id, 
   (SELECT @r := parent_id FROM users_table WHERE id = user_id) AS parent_id, 
   @l := @l + 1 AS level 

   FROM (SELECT @r := 9, @l := 0) val, users_table WHERE @r <> 0 

注意：@r := 9。其中9是子用户的id。

请参阅此处的小提琴

---

上面的查询在 MySQL 8 中已弃用。所以这里是MySQL 8.0 的查询

with recursive parent_users (id, parent_id, level) AS (
  SELECT id, parent_id, 1 level
  FROM users_table
  WHERE id = 9
  union all
  SELECT t.id, t.parent_id, level + 1
  FROM users_table t INNER JOIN parent_users pu
  ON t.id = pu.parent_id
)
SELECT * FROM parent_users;

注意：id = 9。其中 9 是子用户的 id。

在此处查看小提琴

Answer 3

马克·拜尔斯的回答非常棒！

也许有点晚了，但如果您还想在 id =parent_id 时防止无限循环（即，当数据已损坏时），您可以像这样扩展答案：

    SELECT T2.id, T2.name
    FROM (
        SELECT
            @r AS _id,
            @p := @r AS previous,
            (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id,
            @l := @l + 1 AS lvl
        FROM
            (SELECT @r := 5, @p := 0, @l := 0) vars,
            table1 h
        WHERE @r <> 0 AND @r <> @p) T1
    JOIN table1 T2
    ON T1._id = T2.id
    ORDER BY T1.lvl DESC

Answer 4

除了上述解决方案之外：

post
-----
id
title
author

author
------
id
parent_id
name


[post]

id  | title | author |  
----------------------
1   | abc   | 3      |


[author]

| id    | parent_id | name  |   
|---------------------------|
| 1     | 0         | u1    |
| 2     | 1         | u2    |
| 3     | 2         | u3    |
| 4     | 0         | u4    |

包括家长在内的作者可以访问该帖子。

我想检查作者是否有权访问该帖子。

解决方案：

给出帖子作者的id并返回其所有作者和作者的父母

SELECT T2.id, T2.username 
FROM (
    SELECT @r AS _id, 
        (SELECT @r := parent_id FROM users WHERE id = _id) AS parent_id,
        @l := @l + 1
    FROM
        (SELECT @r := 2, @l := 0) vars, 
        users h     
    WHERE @r <> 0) T1 JOIN users T2 
ON T1._id = T2.id;

@r := 2 =>为@r变量赋值。

Answer 5

我使用之前的答案作为示例，以使其更具可读性。

SELECT  @org_id as id,
    (SELECT name FROM test.organizations WHERE id = @org_id) as name,
    (SELECT @org_id := parent_id FROM test.organizations WHERE id = @org_id) AS parent_id
FROM (SELECT @org_id := 4) vars, test.organizations org
WHERE @org_id is not NULL
ORDER BY id;

执行结果如下所示：

（只是为了快速）
要亲自检查，您需要将问题中的值输入数据库 test、表 organizations

CREATE TABLE organizations(
id        int(11) NOT NULL AUTO_INCREMENT,
name      varchar(45) DEFAULT NULL,
parent_id int(11)     DEFAULT NULL,
PRIMARY KEY (id));

insert into organizations values(1, "home", null);
insert into organizations values(2, "about", 1);
insert into organizations values(3, "contact", 1);
insert into organizations values(4, "legal", 2);
insert into organizations values(5, "privacy", 4);
insert into organizations values(6, "products", 1);
insert into organizations values(7, "support", 1);

Answer 6

我认为，使用一个查询没有简单的方法可以做到这一点。

我建议看看 嵌套集，看起来以满足您的需求。

Answer 7

如果您有 slug 而不是 id，则只需运行子查询即可查找子类别的 id。

表 - 类别
|编号 |父 ID |蛞蝓 |
|------------------------------------|
| 1     0      |    u1   |
| 2     1      |    u2    |
| 3     2      |    u3    |
| 4     0      |    u4    |

SELECT T2.id, T2.slug
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parentId FROM categories WHERE id = _id) AS parentId,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := (SELECT id FROM categories WHERE slug = 'u3'), @l := 0) vars,
        categories h
    WHERE @r <> 0) T1
JOIN categories T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

Answer 8

当我为自己的分层表制定解决方案时，我研究了 WP 多级类别模型。根据此处提供的出色答案，我进行了此查询以获取 Wordpress 数据库中的父类别。我不是这方面的专家，但这对我有用，并且可能对寻找此类答案的人有所帮助。

  SELECT T2.term_id,T3.name,T3.slug
    FROM (
        SELECT
            @r AS _id,
            @p := @r AS previous,
            (SELECT @r := parent FROM wp_term_taxonomy WHERE term_id = _id AND taxonomy = 'category') AS parent_id,
            @l := @l + 1 AS lvl
        FROM
            (SELECT @r := 8, @p := 0, @l := 0) vars,
            wp_term_taxonomy h
        WHERE @r <> 0 AND @r <> @p) T1
    JOIN wp_term_taxonomy T2 ON T1._id = T2.term_id AND T2.taxonomy = 'category'
    LEFT JOIN wp_terms T3 ON T3.term_id = T2.term_id
    ORDER BY T1.lvl DESC

Answer 9

据我所知没有。

这篇 Sitepoint 文章可能会对您有所帮助。

您可以使用一个查询检索所有元素，将其存储在一个数组中，然后进行迭代，
如此处所述和此处