sql - 从表中获取所有子节点

Question

我试图从一个表中获取所有子节点，但我的查询获取了一些额外的结果，我不确定为什么

SELECT [pkID]
    ,[parentID]
    ,[CategoryName]
FROM [tblCategory]
WHERE [pkID] = 6
UNION ALL
SELECT [Sub].[pkID]
    ,[Sub].[ParentID]
    ,[Sub].[CategoryName]
FROM [tblCategory] [Sub]
INNER JOIN [tblCategory] AS [Sub2] ON [Sub].[ParentID] = [Sub2].[pkID]

运行此查询时我正在获取其他子节点注意* 在我的 VB.Net 应用程序中(需要调用此 SQL 的地方)0 的 ParentID 是 TreeView 中的根节点。

表格

pkID | ParentID | CategoryName
1    | 6        | Category1
2    | 0        | Category2
3    | 0        | Category3
4    | 0        | Category4
5    | 0        | Category5
6    | 0        | Category6
7    | 0        | Category7
8    | 0        | Category8
9    | 7        | Category9

结果

pkID | parentID | CategoryName
6    | 0        | Category6
1    | 6        | Category1
9    | 7        | Category9 <-- not a child of pkID=6

Answer 1

这是因为您需要在底部 select for tblcategory WHERE [pkID] = 6 上使用相同的 where 语句。没有它，您将获得整个表格的 child 。

所以这样的事情应该可行:

SELECT [pkID]
    ,[parentID]
    ,[CategoryName]
FROM [tblCategory]
WHERE [pkID] = 6
UNION ALL
SELECT [Sub].[pkID]
    ,[Sub].[ParentID]
    ,[Sub].[CategoryName]
FROM [tblCategory] [Sub]
INNER JOIN [tblCategory] AS [Sub2] ON [Sub].[ParentID] = [Sub2].[pkID]
WHERE [sub].[pkID] = 6

如 Juan 所说的那样简化，我应该想到可以在同一个 select 语句上作为单个 where 条件来完成。

SELECT [pkID]
    ,[parentID]
    ,[CategoryName]
FROM [tblCategory]
WHERE [pkID] = 6
     or ParentId = 6

这是一个递归的 cte 方法，用于获取 parentid 的所有 child 、孙子、曾孙等。

;WITH cte AS (
    SELECT [pkID]
       ,[parentID]
       ,[CategoryName]
       -- ,1 AS [Level]
    FROM [tblCategory]
    WHERE [pkID] = 6


    UNION ALL

    SELECT
       t.pkId
       ,t.[parentID]
       ,t.CategoryName
       -- ,[Level] + 1 AS [Level]
    FROM
       [tblCategory] t
       INNER JOIN cte c
       ON t.ParentId = c.pkId
)

SELECT *
FROM cte