获取根父级

发布于 2024-12-21 02:27:16 字数 655 浏览 0 评论 0原文

+--------+---------+-----------+
|   id   | title   | parent_id |
+--------+---------+-----------+
|    1   | Lvl-1   |   null    |
+--------+---------+-----------+
|    2   | Lvl-2   |   null    |
+--------+---------+-----------+
|    3   | Lvl-11  |     1     |
+--------+---------+-----------+
|    4   | Lvl-12  |     1     |
+--------+---------+-----------+
|    5   | Lvl-121 |     4     |
+--------+---------+-----------+

我实际上如何获取每行的根父级
例如，id 5 的行的父级为 id 4，而 id 4 的父级为 id 1，因此id 5 的根 ID 是 id 1
我不知道如何做到这一点，有没有办法通过仅使用 1 个查询来解决这个问题

原文

+--------+---------+-----------+
|   id   | title   | parent_id |
+--------+---------+-----------+
|    1   | Lvl-1   |   null    |
+--------+---------+-----------+
|    2   | Lvl-2   |   null    |
+--------+---------+-----------+
|    3   | Lvl-11  |     1     |
+--------+---------+-----------+
|    4   | Lvl-12  |     1     |
+--------+---------+-----------+
|    5   | Lvl-121 |     4     |
+--------+---------+-----------+

How do i actualy get root parent for each row
For example, row with id 5 have parent with id 4 and id 4 have parent with id 1, so root id for id 5 is id 1
I dont have any idea on how to do it and is there a way to solve this by using only 1 query

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

纸短情长 2024-12-28 02:27:16

这是一个简短的查询，执行您所要求的操作，假设您的表名为 foo 并且您想知道的根：

SELECT f.id, f.title
FROM (
    SELECT @id AS _id, (SELECT @id := parent_id FROM foo WHERE id = _id)
    FROM (SELECT @id := <id>) tmp1
    JOIN foo ON @id IS NOT NULL
    ) tmp2
JOIN foo f ON tmp2._id = f.id
WHERE f.parent_id IS NULL

Here is a short query doing what you're asking, assuming your table is called foo and that you want to know the root of <id>:

SELECT f.id, f.title
FROM (
    SELECT @id AS _id, (SELECT @id := parent_id FROM foo WHERE id = _id)
    FROM (SELECT @id := <id>) tmp1
    JOIN foo ON @id IS NOT NULL
    ) tmp2
JOIN foo f ON tmp2._id = f.id
WHERE f.parent_id IS NULL

回复收藏 0 原文