棘手的选择语句

我有一个包含类别的表，每个类别都有一个 ID、一个名称和一个 ParentID。问题是有3个级别，父类别、子类别和子类别。

我可以用一个简单的方法提取父类别SELECT and a WHERE ParentID IS NULL条款如下：

SELECT *
FROM Category
WHERE ParentID IS NULL

然而，一个WHERE ParentID IS NOT NULL子句将返回子类别和子类别。

我正在寻找一种仅提取子类别和子类别的方法。

通常，对于此类问题，最好使用使用公用表表达式的递归查询 http://msdn.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx。像这样：

;WITH CategoriesTree(CategoryID, CategoryName, ParentName, CategoryLevel)
AS
(
   SELECT 
     c.ID, 
     c.Name, 
     CAST('No Parent' AS VARCHAR(50)) AS ParentName, 
     0 AS CategoryLevel
   FROM @Categories c
   WHERE c.ParentID IS NULL
   UNION ALL
   SELECT c.ID, c.Name, p.CategoryName, p.CategoryLevel + 1
   FROM CategoriesTree p
   INNER JOIN @Categories c ON c.ParentID = p.CategoryID
)
SELECT * 
FROM CategoriesTree
Where CategoryLevel = some id;

SQL 小提琴演示 http://www.sqlfiddle.com/#!3/d41d8/6963

这会给你：

CATEGORYID       CATEGORYNAME         PARENTNAME      CATEGORYLEVEL
    1         Root Cateogry         No Parent               0
    2         Sub Cateogry 1        Root Cateogry           1
    3         Sub Cateogry 2        Root Cateogry           1
    4         Sub Cateogry 3        Root Cateogry           1
    8         sub Cateogry 1 of 3   Sub Cateogry 3          2
    7         Sub Cateogry 1 of 2   Sub Cateogry 2          2
    5         Sub Cateogry 1 of 1   Sub Cateogry 1          2
    6         sub Cateogry 2 of 1   Sub Cateogry 1          2

这是如何运作的？

使用此查询，您可以控制要选择的类别级别。例如，对于我在之前的演示中使用的示例数据，这里是类别树：

                        1: RootCategory                Category Level:  0
                               |
                               |
                 ---------------------------- 
                 |             |            |
                 |             |            |
              2: Sub1        3: Sub2      4: sub3      Category Level:   1
                 |             |            |
          ------------         |            |
          |          |         |            |
          |          |         |            |
     5: Sub1of1  6: Sub2of1   7: sub1of2   8: sub1of3  Category Level:   2

此查询将为您提供带有新生成的类别树CategoryLevel柱子。

注意：在我在演示中使用的示例数据中，只有一个父类别（带有parentid一片空白）。但是，如果有很多父类别，查询将正常工作。这是因为 CTE 的锚查询，即：

SELECT 
     c.ID, 
     c.Name, 
     CAST('No Parent' AS VARCHAR(50)) AS ParentName, 
     0 AS CategoryLevel
FROM @Categories c
WHERE c.ParentID IS NULL;

然后，您可以使用生成的列CategoryLevel仅选择您感兴趣的级别的子类别。

例如，如果只需要选择根类别的第一个子类别的子类别，则可以使用谓词获取这些类别CategoryLevel = 2:

;WITH CategoriesTree(CategoryID, CategoryName, ParentName, CategoryLevel)
AS
(
    ...
)
SELECT * 
FROM CategoriesTree
WHERE CategoryLevel = 2;

这会给你：

CATEGORYID       CATEGORYNAME         PARENTNAME      CATEGORYLEVEL
    8        sub Cateogry 1 of 3    Sub Cateogry 3          2
    7        Sub Cateogry 1 of 2    Sub Cateogry 2          2
    5        Sub Cateogry 1 of 1    Sub Cateogry 1          2
    6        sub Cateogry 2 of 1    Sub Cateogry 1          2

SQL 小提琴演示 http://www.sqlfiddle.com/#!3/d41d8/6965