SQL Server 2005中的分层查询 [英] Hierarchical query in SQL Server 2005

问题描述

我在SQL Server 2005的表中有一个数据结构，表示一个相关对象链.每个对象可以在许多步骤中进行替换.我想执行一个查询，该查询返回替换链中的所有对象和每个对象的叶子.

I have a data structure inside a table in SQL Server 2005 representing a chain of related objects. Each object can have replacements in many steps. I want to perform a query that returns all objects and each object's leaf in the replacement chain.

数据:

id  replacement
1   null
2   3
3   null
4   5
5   6
6   null

结果应为:

id  replacement
1   null
2   3
3   null
4   6
5   6
6   null

我相信递归CTE是一个不错的选择，但我无法绕开它.该问题的一个限制是，由于数据库不在我的控制范围之内，所以我无法更改数据结构.

I believe that a recursive CTE would be a good way to go, but I can't wrap my head around it. A constraints to the problem is that I can't change the data structure, since the database is not in my control.

推荐答案

看看这个

DECLARE @Table TABLE(
        ID INT,
        ReplacementID INT
)

INSERT INTO @Table (ID,ReplacementID) SELECT 1, NULL
INSERT INTO @Table (ID,ReplacementID) SELECT 2, 3
INSERT INTO @Table (ID,ReplacementID) SELECT 3, NULL
INSERT INTO @Table (ID,ReplacementID) SELECT 4, 5
INSERT INTO @Table (ID,ReplacementID) SELECT 5, 6
INSERT INTO @Table (ID,ReplacementID) SELECT 6, NULL

INSERT INTO @Table (ID,ReplacementID) SELECT 7, 8
INSERT INTO @Table (ID,ReplacementID) SELECT 8, 9
INSERT INTO @Table (ID,ReplacementID) SELECT 9, 10
INSERT INTO @Table (ID,ReplacementID) SELECT 10, NULL

SELECT * FROM @Table

;WITH repl AS (
    SELECT  *, 1 AS Depth
    FROM    @Table t
    UNION   ALL
    SELECT  r.ID,
            t.ReplacementID,
            r.Depth + 1
    FROM    repl r INNER JOIN
            @Table t ON r.ReplacementID = t.ID
    WHERE   t.ReplacementID IS NOT NULL
)
SELECT  repl.ID,
        repl.ReplacementID
FROM    (
            SELECT  ID,
                    MAX(Depth) Depth
            FROM    repl
            GROUP BY ID
        ) Depths INNER JOIN
        repl    ON  Depths.ID = repl.ID
                AND Depths.Depth = repl.Depth
ORDER BY 1

这篇关于SQL Server 2005中的分层查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！