递归找到给定孩子的所有祖先 [英] Recursively find all ancestors given the child

问题描述

给定一个孩子 id，我需要返回一个包含该孩子的所有父母以及他们的父母的查询，直到我到达根父母.例如，给定以下数据:

Given a child id, I need to return a query containing all parents of that child as well as their parents till I get to the root parent. For example, given this data:

ID / Parent ID
1  /  0
2  /  1
3  /  2
4  /  0
5  /  3

因此，如果我传入 ID 5，我希望获得带有结果的查询:

So if I passed in ID 5 I would like to get a query with the results:

ID / Parent ID
1  /  0
2  /  1
3  /  2

此表不适用于hierarchyid 类型，因此我怀疑这需要使用CTE 来完成，但不知道如何完成.如果可以在 SQL 查询/过程中完成，任何帮助将不胜感激.

This table does not work with a hierarchyid type so I suspect that this will need to be done with a CTE, but have no clue how. If it can be done in an SQL query / proc, any help would be appreciated.

谢谢

推荐答案

这或多或少是您想要的:

-- CTE to prepare hierarchical result set
;WITH #results AS
(
    SELECT  id, 
            parentid 
    FROM    [table] 
    WHERE   id = @childId
    UNION ALL
    SELECT  t.id, 
            t.parentid 
    FROM    [table] t
            INNER JOIN #results r ON r.parentid = t.id
)
SELECT  *
FROM    #results;

参考:

• CTE:公用表表达式

工作示例:

-- create table with self lookup (parent id)
CREATE TABLE #tmp (id INT, parentid INT);

-- insert some test data
INSERT INTO #tmp (id, parentid) 
SELECT 1,0 UNION ALL SELECT 2,1 UNION ALL SELECT 3,2
UNION ALL SELECT 4,0 UNION ALL SELECT 5,3;

-- prepare the child item to look up
DECLARE @childId INT;
SET @childId = 5;

-- build the CTE
WITH #results AS
(
    SELECT  id, 
            parentid 
    FROM    #tmp 
    WHERE id = @childId
    UNION ALL
    SELECT  t.id, 
            t.parentid 
    FROM    #tmp t
            INNER JOIN #results r ON r.parentid = t.id
)

-- output the results
SELECT  * 
FROM    #results 
WHERE   id != @childId 
ORDER BY id;

-- cleanup
DROP TABLE #tmp;

输出:

1 |0
2 |1
3 |2

1 | 0
2 | 1
3 | 2

这篇关于递归找到给定孩子的所有祖先的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！