SQL Server中的层次结构 - 多个级别

时间：2022-09-26 09:51:55

I have the following requirement,

我有以下要求,

Input

ID Parent_ID
------------
1  0
2  0
3  10
4  0
5  3
6  20
7  3
8  21
9  3
10  0
20  0
21  0
  • When I use @ID = 1 the result has to be 1.

    • 当我使用@ID = 1时,结果必须为1。

  • When I use @ID = 6 the result has to be 20.

    • 当我使用@ID = 6时,结果必须是20。

  • When I use @ID = 5 or 7, 9 the result has to be 10, because 5 -> 3 (Parent_ID -> Str_ID) -> 10 (Parent_ID -> Str_ID) -> 0 (stop is 0 in Parent_ID), so the result 10.

    • 当我使用@ID = 5或7,9结果必须是10,因为5 - > 3(Parent_ID - > Str_ID) - > 10(Parent_ID - > Str_ID) - > 0(在Parent_ID中停止为0),所以结果10。

So my task is looking for ID as long as I'll find 0 in Parent_ID.

所以我的任务是寻找ID,只要我在Parent_ID中找到0。

Output:

@ID Result
----------
1  1
2  2 
3  10
4  4
5  10
6  20
7  10
8  21
9  10
10 10
20 20
21 21

1 个解决方案

#1

1  

A recursive CTE with rollup can do this: MSDN Article

具有汇总的递归CTE可以执行此操作:MSDN文章

BEGIN
--Setup some data
DECLARE @tmp as TABLE (ID int, ParentID int);
INSERT INTO @tmp
VALUES 
(1 , 0 ),
(2 , 0 ),
(3 , 10),
(4 , 0 ),
(5 , 3 ),
(6 , 20),
(7 , 3 ),
(8 , 21),
(9 , 3 ),
(10,  0),
(20,  0),
(21,  0),
(44,  5),
(83,  44),
(46,  83),
(23,  7);

WITH Parents (ID, ParentID, TopParent) AS (
    SELECT ID, ParentID, ID
        FROM @tmp
        WHERE ParentID = 0
    UNION ALL
    SELECT t.ID, t.ParentID, p.TopParent
        FROM Parents p 
        JOIN @tmp t on t.ParentID = p.id)
SELECT * FROM Parents
--Or to get just the ID and top parent: SELECT ID, TopParent FROM Parents 

END

Results:

ID          ParentID    TopParent
----------- ----------- -----------
1           0           1
2           0           2
4           0           4
10          0           10
20          0           20
21          0           21
8           21          21
6           20          20
3           10          10
5           3           10
7           3           10
9           3           10
23          7           10
44          5           10
83          44          10
46          83          10

#1

1  

A recursive CTE with rollup can do this: MSDN Article

具有汇总的递归CTE可以执行此操作:MSDN文章

BEGIN
--Setup some data
DECLARE @tmp as TABLE (ID int, ParentID int);
INSERT INTO @tmp
VALUES 
(1 , 0 ),
(2 , 0 ),
(3 , 10),
(4 , 0 ),
(5 , 3 ),
(6 , 20),
(7 , 3 ),
(8 , 21),
(9 , 3 ),
(10,  0),
(20,  0),
(21,  0),
(44,  5),
(83,  44),
(46,  83),
(23,  7);

WITH Parents (ID, ParentID, TopParent) AS (
    SELECT ID, ParentID, ID
        FROM @tmp
        WHERE ParentID = 0
    UNION ALL
    SELECT t.ID, t.ParentID, p.TopParent
        FROM Parents p 
        JOIN @tmp t on t.ParentID = p.id)
SELECT * FROM Parents
--Or to get just the ID and top parent: SELECT ID, TopParent FROM Parents 

END

Results:

ID          ParentID    TopParent
----------- ----------- -----------
1           0           1
2           0           2
4           0           4
10          0           10
20          0           20
21          0           21
8           21          21
6           20          20
3           10          10
5           3           10
7           3           10
9           3           10
23          7           10
44          5           10
83          44          10
46          83          10
标签：

相关文章