sql - 返回层次结构中第N级的类别的名称(parentId -1的类别为第一级)

数据样本

WITH sample_data AS (
SELECT  CategoryId,
        ParentCategoryId,
        Name,
        Keywords
FROM (VALUES
(100, -1,   'business',         'Money'),
(200, -1,   'tutoring',         'teaching'),
(101, 100,  'Accountting',      'taxes'),
(102, 100,  'Taxation',         NULL),
(201, 200,  'Computer',         NULL),
(103, 101,  'Corporate Tax',    NULL),
(202, 201,  'operating system', NULL),
(109, 101,  'Small business Tax', NULL)) as c(CategoryId, ParentCategoryId, Name, Keywords)
)

样本输入/输出：

Input: 2
Output: 101, 102, 201

Input: 3
Output: 103, 109, 202

我一直在尝试按班分组学习，但是没有用，有人可以帮我用递归CTE来做吗（我对此很陌生）

TIA

最佳答案

您可以使用以下查询：

DECLARE @level INT = 2

;WITH CTE AS (
   -- Start from root categories
   SELECT CategoryId, ParentCategoryId, Name, Keywords, level = 1
   FROM Cat
   WHERE ParentCategoryId = -1

   UNION ALL

   -- Obtain next level category
   SELECT c1.CategoryId, c1.ParentCategoryId,
          c1.Name, c1.Keywords, level = c2.level + 1
   FROM Cat AS c1
   INNER JOIN CTE AS c2 ON c1.ParentCategoryId   = c2.CategoryId
   WHERE c2.level < @level -- terminate if specified level has been reached
)
SELECT CategoryId
FROM CTE
WHERE level = @level

输出：

CategoryId
==========
201
101
102

关于sql - 返回层次结构中第N级的类别的名称(parentId -1的类别为第一级)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35767971/