递归查询SQL Server不能按预期工作

debonaire 发表于 Dev

德博奈尔

在此先感谢您的帮助。我对MS SQL db还是很陌生，但是我想知道为什么我下面的MSSQL递归查询不返回我期望的值。我已经完成了研究，最底层是我想出的代码。可以说我有下表...

CategoryID    ParentID    SomeName
1             0           hmm
2             0           err
3             0           woo
4             3           ppp
5             4           ttt

我希望下面的查询返回3 4 5。我基本上想根据我在递归查询中传递的类别ID来获取类别ID的层次结构列表在其自身以下。感谢您的协助。

GO
WITH RecursiveQuery (CategoryID)
AS
(
-- Anchor member definition
    SELECT a.CategoryID 
    FROM [SomeDB].[dbo].[SomeTable] AS a
    WHERE a.ParentID = CategoryID 
    UNION ALL
-- Recursive member definition
    SELECT b.CategoryID
    FROM [SomeDB].[dbo].[SomeTable] AS b
    INNER JOIN RecursiveQuery AS d
        ON d.CategoryID = b.ParentID
)
-- Statement that executes the CTE
SELECT o.CategoryID
FROM [SomeDB].[dbo].[SomeTable] AS o
INNER JOIN RecursiveQuery AS d
    ON d.CategoryID = 3
GO

伊万·斯塔诺汀（Ivan Starostin）

如果要从特定根目录树：

DECLARE @rootCatID int = 3

;WITH LessonsTree (CatID)
AS
(
    SELECT a.CategoryID
    FROM [EducationDatabase].[dbo].[LessonCategory] AS a
    WHERE a.CategoryID = @rootCatID ---<<<

    UNION ALL

    SELECT b.CategoryID
    FROM LessonsTree as t
    INNER JOIN [EducationDatabase].[dbo].[LessonCategory] AS b
        ON b.ParentID = t.CatID
)
SELECT o.*
FROM LessonsTree t
INNER JOIN [EducationDatabase].[dbo].[LessonCategory] AS o
    ON o.CategoryID = t.CatID