Geek asks and answers

Is there a way to select parent identifiers in SQL without recursion or loop?

I have a table with the following columns: group_id, parent_id, name

In this table, parent_id is the group_id of another record. There is a parental relationship with children from 1 to N. This forms a hierarchy with only one top-level group that has NULL for parent_id. There may be arbitrary depth, but in practice my hierarchy should not exceed 20 levels.

I would like to get every ancestor (parent parent, etc.) of a group with a given group_id. I am concerned about the specific way to return it.

I use MS SQL 2005, but I am also interested in solutions using other RDBMSs.

I found several similar questions, but they all seem to be broken down into recursion, cyclic or nested sets. I cannot use nested sets because I cannot change the data structure. I would like to avoid recursion or loop where possible, or at least understand why this is not possible.

Here are some questions I found while researching this:

How to choose parent identifiers

Sql recursion without recursion

+3
source share
3 answers

The operation is essentially closed. Since each node has no final relation to its root, you must go through it in order to detect it.

, , , N, N LEFT OUTER JOIN , .

, , N, , SQL, " "

, , .

+1

, , :

DECLARE
    @parentId1 int
   ,@parentId2 int
   ...
   ,@parentId19 int
   ,@parentId20 int

SELECT
    @parentId1 = parent_id
FROM
    myTable
WHERE
    group_id = <someid>

SELECT
    @parentId2 = parent_id
FROM
    myTable
WHERE
    group_id = @parentId1

. , , . , .

. X N , N . ? . , - ( ). ? , , .

, MSSQL, , 16. , MSSQL.

- :

-- Temp table will hold the results starting from the ID of the source item
-- through all its ancestors in ascending order
DECLARE @table TABLE (
    sequence int IDENTITY(1, 1)
   ,group_id int
)

DECLARE @groupId int

SELECT @groupId = <someid>

-- Loop backwards through the group hierarchy inserting all parent IDs
-- into the temporary table
WHILE @groupId IS NOT NULL
BEGIN
    INSERT INTO @table (
        group_id
    )

    VALUES (
        @groupId
    )

    -- Get the ID of the group parent ready to loop again
    SELECT @groupId = parent_id
    FROM mutable
    WHERE group_id = @groupId
END

-- Print the results
SELECT group_id
FROM @table

, , , , , .

+1

, , :

INSERT INTO #T(group_id, parent_id) SELECT group_id, parent_id FROM Your_Table

SQL :

INSERT INTO #T(group_id, parent_id) SELECT T2.group_id, T1.parent_id FROM #T T1 JOIN #T T2 ON T2.parent_id = T1.group_id LEFT JOIN #T C ON T2.group_id = C.group_id AND T1.parent_id = C.parent_id  AND C.group_id IS NULL
INSERT INTO #T(group_id, parent_id) SELECT T2.group_id, T1.parent_id FROM #T T1 JOIN #T T2 ON T2.parent_id = T1.group_id LEFT JOIN #T C ON T2.group_id = C.group_id AND T1.parent_id = C.parent_id  AND C.group_id IS NULL
INSERT INTO #T(group_id, parent_id) SELECT T2.group_id, T1.parent_id FROM #T T1 JOIN #T T2 ON T2.parent_id = T1.group_id LEFT JOIN #T C ON T2.group_id = C.group_id AND T1.parent_id = C.parent_id  AND C.group_id IS NULL
INSERT INTO #T(group_id, parent_id) SELECT T2.group_id, T1.parent_id FROM #T T1 JOIN #T T2 ON T2.parent_id = T1.group_id LEFT JOIN #T C ON T2.group_id = C.group_id AND T1.parent_id = C.parent_id  AND C.group_id IS NULL
INSERT INTO #T(group_id, parent_id) SELECT T2.group_id, T1.parent_id FROM #T T1 JOIN #T T2 ON T2.parent_id = T1.group_id LEFT JOIN #T C ON T2.group_id = C.group_id AND T1.parent_id = C.parent_id  AND C.group_id IS NULL

, , 32 . (2 ^ 5 = 32 32 > 20).

" ", , INSERT , 20 . , INSERT . , , .

+1
source

More articles:


All Articles