sql - CTE 亲子展示 sibling

Question

我有一个 CTE 查询，它使用递归显示树。这在显示整棵树时效果很好。但我想将 ID 作为变量传递，并包含每个当前节点的兄弟节点。

测试代码:

DECLARE @TT TABLE 
(
ID int,
Name varchar(25),
ParentID int,
SortIndex int
)

INSERT @TT 
SELECT 1, 'A', NULL, 1 UNION ALL
SELECT 2, 'B_1', 3, 1 UNION ALL
SELECT 3, 'B', 1, 2 UNION ALL
SELECT 4, 'B_2', 3, 2 UNION ALL
SELECT 5, 'C', 1, 3 UNION ALL
SELECT 6, 'C_2', 5, 2  UNION ALL
SELECT 7, 'A_1', 1, 1 UNION ALL
SELECT 8, 'A_2', 1, 2 UNION ALL
SELECT 9, 'C_1', 5, 1 


;WITH CTETree
AS
(
    SELECT *, CAST(NULL AS VARCHAR(25)) AS ParentName, 1 AS Lev,
    CAST(ROW_NUMBER() OVER(ORDER BY SortIndex) AS VARBINARY(MAX)) AS SortPath
    FROM @TT
    WHERE ParentID IS NULL

    UNION ALL

    SELECT F.*, CTETree.Name AS ParentName, Lev + 1,
    SortPath + CAST(ROW_NUMBER() OVER(ORDER BY F.SortIndex) AS BINARY(32))
    FROM @TT AS F
    INNER JOIN CTETree
    ON F.ParentID = CTETree.ID 
)

SELECT * FROM CTETree
    ORDER BY SortPath

/*
DESIRED RESULT:

WHEN ID = 3 PASSED IN:

1   A   NULL    1   NULL    1
3   B   1   2   A   2
2   B_1 3   1   B   3
4   B_2 3   2   B   3
5   C   1   3   A   2

WHEN ID = 1 PASSED IN:

1   A   NULL    1   NULL    1
3   B   1   2   A   2
5   C   1   3   A   2

WHEN ID = 9 PASSED IN:

1   A   NULL    1   NULL    1
3   B   1   2   A   2
5   C   1   3   A   2
9   C_1 5   1   C   3
6   C_2 5   2   C   3

*/

SQL-Fiddle:http://sqlfiddle.com/#!3/d41d8/5526

Answer 1

只有一次我遇到了一个问题，A_1和A_2消失了。因此添加异常(exception)(in case 子句)。如果您需要此记录，请删除此表达式 AND f.ID != 7 AND f.ID != 8。

DECLARE @ID int = 3 -- variable wich you want pass
DECLARE @Matched int = (SELECT CASE WHEN ParentID = 1 THEN ID ELSE ParentID END FROM @TT WHERE ID = @ID)
;WITH CTETree
AS
(
 SELECT *, CAST(NULL AS VARCHAR(25)) AS ParentName, 1 AS Lev,
        CAST(ROW_NUMBER() OVER(ORDER BY SortIndex) AS VARBINARY(MAX)) AS SortPath,
        0 AS Matched
 FROM @TT
 WHERE ParentID IS NULL
 UNION ALL
 SELECT F.*, cte.Name AS ParentName, cte.Lev + 1,
        SortPath + CAST(ROW_NUMBER() OVER(ORDER BY F.SortIndex) AS BINARY(32)),
        CASE WHEN (cte.ID = 1 AND f.ID != 7 AND f.ID != 8)
        OR (cte.ID = @Matched AND cte.Lev + 1 > 2)
        THEN 1 END AS Matched
 FROM @TT AS F INNER JOIN CTETree cte
 ON F.ParentID = cte.ID
)
SELECT ID, Name, ParentID, SortIndex, ParentName, Lev FROM CTETree
WHERE Matched IS NOT NULL
ORDER BY SortPath

演示 SQLFiddle