查找顶级父 ID [英] Finding Top Level Parent ID

问题描述

在一张表中，我有两列如下

In one table I have two columns like below

ID  ParentID
1   0x0
2   1
3   2
9   0x0
5   9
6   5
25  0x0
30  25

如何获取顶级父ID
结果应该是这样的

How to get top level parent ID
The result should look like

ID   TopParentID
3    1
6    9
30   25

推荐答案

又一个更短的 CTE :

Yet another shorter CTE :

WITH cte AS(
      SELECT *, id AS topparent 
      FROM t 
      WHERE parentid IS NULL
  UNION ALL
      SELECT t.*, c.topparent 
      FROM t JOIN cte c ON c.id = t.parentid
      WHERE t.id <> t.parentid
)
SELECT * FROM cte

在这里测试:SQL Fiddle

EDIT :这个该死的 SQLfiddle 又没有响应了.下面测试 DDL 代码

EDIT : This damn SQLfiddle is unresponsive again. Test DDL Code below

CREATE  table t (id INT, parentid INT);
INSERT INTO T VALUES (1  , NULL );
INSERT INTO T VALUES (2  , 1    );           
INSERT INTO T VALUES (3  , 2    );          
INSERT INTO T VALUES (9  , NULL );          
INSERT INTO T VALUES (5  , 9    );          
INSERT INTO T VALUES (6  , 5    );          
INSERT INTO T VALUES (25 , NULL );          
INSERT INTO T VALUES (30 , 25   );

这篇关于查找顶级父 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！