CTE亲子显示兄弟姐妹

Question

我有一个CTE Query，它使用递归显示树。在展示整棵树时，这很棒。但是我想将ID作为变量传递，并包括每个当前节点的兄弟姐妹。

testcode：

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的问题中。因此，添加异常（如果子句）。如果您需要此记录，请删除此表达式和F.ID！= 7和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