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

Question

数据示例

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

我一直在按类(class)进行分组尝试，但它不起作用，有人可以帮我用递归 CTE 来做这件事吗(我对此很陌生)

TIA

Answer 1

您可以使用以下查询:

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