MySQL:获取父子结构的根节点 [英] MySQL: Get Root Node of Parent-Child Structure

问题描述

我有一个与此类似的表:

I have a table similar to this:

=================
| Id | ParentId |
=================
| 1  | 0        |
-----+-----------
| 2  | 1        |
-----+-----------
| 3  | 0        |
-----+-----------
| 4  | 3        |
-----+-----------
| 5  | 3        |
-----+-----------
| 6  | 0        |
-----+-----------
| 7  | 6        |
-----+-----------
| 8  | 7        |
-----------------

给出一个ID，我需要知道它的根节点" ID.所以，

Given an Id, I need to know its root "node" Id. So,

• 给出1，返回1
• 给出2，返回1
• 给出3，返回3
• 给出4，返回3
• 给出5，返回3
• 给出6，返回6
• 给出7，返回6
• 给出8，返回7

层次结构的级别没有限制.有可以满足我需要的SQL吗?

There is no limit to the levels of the hierarchy. Is there a SQL that can do what I need?

推荐答案

在MySQL中很难做到这一点，因为它尚不支持递归公用表表达式.

This is quite difficult to do in MySQL because it doesn't yet support recursive common table expressions.

我建议使用嵌套集模型，或者将根节点存储在行中，并在结构更改时对其进行更新.

I'd suggest instead using a nested sets model, or else storing the root node in the row and updating it as the structure changes.

这篇关于MySQL:获取父子结构的根节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！