如何在SQL Server中将层次结构关系分组在一起

Question

我在表“ 示例* ” 中有一个列名称“ 父级” 和“ 子级” ，以下是表数据 *

|     Parent          |     Child        |
|---------------------|------------------|
|      100            |      101         |
|---------------------|------------------|
|      101            |      102         |
|---------------------|------------------|
|      200            |     201          |
|---------------------|------------------|
|      103            |      102         |
|---------------------|------------------|
|      202            |      201         |
|---------------------|------------------|

如果我将输入设为 100，那么 我应该得到的结果为 100,101,102,103。 由于 100- > 101-> 102->
103
，而且如果我将输入设为 102， 则它也将得到与上述相同的结果。 102- > 101-> 100和102->
103
。我只需要使用存储过程来实现此目的。

以下是我正在尝试的示例代码

CREATE PROCEDURE GetAncestors(@thingID varchar(MAX))
AS
BEGIN
SET NOCOUNT ON;

WITH
CTE
AS
(
    SELECT
        Example.Parent, Example.Child
    FROM Example
    WHERE Parent = @thingID or Child = @thingID

    UNION ALL

    SELECT
        Example.Parent, Example.Child
    FROM
        CTE
        INNER JOIN Example ON Example.Parent = CTE.Child
)
SELECT
    Parent AS Result
FROM CTE

UNION

SELECT
    Child AS Result
FROM CTE
;

END
GO

Answer 1

您尝试的问题是在一开始就进行过滤。如果我是对的，则您希望按数据的升序或后代关系，或它们的混合关系对数据进行聚类（将所有数据分组在一起）。例如，ID100具有child
101，具有另一个child 102，但是102具有父级，103并且您希望该100, 101, 102, 103集合中任何输入的结果均为这四个（）。这就是为什么您不能一开始就进行过滤的原因，因为您没有任何方法可以知道哪个关系将在另一个关系中链接在一起。

解决这个问题并不像看起来那么简单，并且您将无法仅通过1次递归来解决它。

以下是我很久以前就将所有这些关系分组在一起的解决方案。请记住，对于大型数据集（超过10万个），可能要花一些时间进行计算，因为它必须首先识别所有组，然后最后选择结果。

CREATE PROCEDURE GetAncestors(@thingID INT)
AS
BEGIN

    SET NOCOUNT ON

    -- Load your data
    IF OBJECT_ID('tempdb..#TreeRelationship') IS NOT NULL
        DROP TABLE #TreeRelationship

    CREATE TABLE #TreeRelationship (
        RelationID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
        Parent INT,
        Child INT,
        GroupID INT)

    INSERT INTO #TreeRelationship (
        Parent,
        Child)
    SELECT
        Parent = D.Parent,
        Child = D.Child
    FROM
        Example AS D
    UNION -- Data has to be loaded in both ways (direct and reverse) for algorithm to work correctly
    SELECT
        Parent = D.Child,
        Child = D.Parent
    FROM
        Example AS D


    -- Start algorithm
    IF OBJECT_ID('tempdb..#FirstWork') IS NOT NULL
        DROP TABLE #FirstWork

    CREATE TABLE #FirstWork (
        Parent INT,
        Child INT,
        ComponentID INT)

    CREATE CLUSTERED INDEX CI_FirstWork ON #FirstWork (Parent, Child)

    INSERT INTO #FirstWork (
        Parent, 
        Child,
        ComponentID)
    SELECT DISTINCT 
        Parent = T.Parent,
        Child = T.Child, 
        ComponentID = ROW_NUMBER() OVER (ORDER BY T.Parent, T.Child)
    FROM 
        #TreeRelationship AS T


    IF OBJECT_ID('tempdb..#SecondWork') IS NOT NULL
        DROP TABLE #SecondWork

    CREATE TABLE #SecondWork (
        Component1 INT,
        Component2 INT)

    CREATE CLUSTERED INDEX CI_SecondWork ON #SecondWork (Component1)


    DECLARE @v_CurrentDepthLevel INT = 0

    WHILE @v_CurrentDepthLevel < 100 -- Relationships depth level can be controlled with this value
    BEGIN

        SET @v_CurrentDepthLevel = @v_CurrentDepthLevel + 1

        TRUNCATE TABLE #SecondWork

        INSERT INTO #SecondWork (
            Component1,
            Component2)
        SELECT DISTINCT
            Component1 = t1.ComponentID,
            Component2 = t2.ComponentID
        FROM 
            #FirstWork t1
            INNER JOIN #FirstWork t2 on 
                t1.child = t2.parent OR 
                t1.parent = t2.parent
        WHERE
            t1.ComponentID <> t2.ComponentID

        IF (SELECT COUNT(*) FROM #SecondWork) = 0
            BREAK

        UPDATE #FirstWork SET 
            ComponentID = CASE WHEN items.ComponentID < target THEN items.ComponentID ELSE target END
        FROM 
            #FirstWork items
            INNER JOIN (
                SELECT
                    Source = Component1, 
                    Target = MIN(Component2)
                FROM
                    #SecondWork
                GROUP BY
                    Component1
            ) new_components on new_components.source = ComponentID


        UPDATE #FirstWork SET
            ComponentID = target
        FROM #FirstWork items
            INNER JOIN(
                SELECT
                    source = component1, 
                    target = MIN(component2)
                FROM
                    #SecondWork
                GROUP BY
                    component1
            ) new_components ON new_components.source = ComponentID

    END

    ;WITH Groupings AS
    (
        SELECT 
            parent,
            child,
            group_id = DENSE_RANK() OVER (ORDER BY ComponentID  DESC)
        FROM
            #FirstWork
    )
    UPDATE FG SET
        GroupID = IT.group_id
    FROM
        #TreeRelationship FG
        INNER JOIN Groupings IT ON
            IT.parent = FG.parent AND
            IT.child = FG.child


    -- Select the proper result
    ;WITH IdentifiedGroup AS
    (
        SELECT TOP 1
            T.GroupID
        FROM
            #TreeRelationship AS T
        WHERE
            T.Parent = @thingID
    )
    SELECT DISTINCT
        Result = T.Parent
    FROM
        #TreeRelationship AS T
        INNER JOIN IdentifiedGroup AS I ON T.GroupID = I.GroupID

END

你会看到，@thingID价值100，101，102和103结果是这四个，和值200，201并且202结果是这三种。

我很确定这不是一个最佳解决方案，但是它可以提供正确的输出，而且我不需要对其进行调整，因为它可以快速满足我的要求。