SQL Server CTE 递归 - 将树追溯到每个节点的特定树级别

Question

我有两个表，Item（ItemID、CatID、Name）和Category（CatID、ParentID、Name、CatLevel）。 类别是项目所属类别的树形层次结构。该表还跟踪每个节点所在的类别级别。下面是一个示例：

1. Animal (CatLevel = 1)
    2. Dog (CatLevel = 2)
        3. Beagle (CatLevel = 3)
        4. Labrador (CatLevel = 3)
            5. Yellow (CatLevel = 4)
            6. Chocolate (CatLevel = 4)
            7. Black (CatLevel = 4)
    8. Cat (CatLevel = 2)
        9. Tabby (CatLevel = 3)
    10. Horse (CatLevel = 2)
        11. Stallion (CatLevel = 3)

我正在尝试提出一个查询，该查询为我提供了具有常规 CatID 和我指定级别（例如级别 2）的 CatID 的项目列表。示例输出：

Item      CatID    LevelTwoCatID
______    _____    _____________
Dog       2        2
Beagle    3        2
Labrador  4        2
Yellow    5        2
Chocolate 6        2
Black     7        2
Cat       8        8
Tabby     9        8
Horse     10       10
Stallion  11       10

我知道如何追溯到单个节点的类别，但我不知道如何进行更通用的查询来为每个节点提供“LevelTwoCatID”。以下是我的做法：

WITH Tree AS
(
    SELECT * FROM Category WHERE CatID = 6 -- Chocolate lab, change for some other

    UNION ALL

    SELECT * FROM Category AS a
    INNER JOIN Tree t ON a.CatID = t.ParentID
)
SELECT * FROM Tree WHERE CatLevel = 2

如何获取所有节点/叶子的 CatLevel = 2 节点 ID？

谢谢！

Answer 1

我知道这篇文章已经很老了，但我是这样做的。

在我的表中有几个根类别，因此我使用 @RootId 来指定我要查找哪一个。根类别的 ParentID 设置为 NULL。

如果您的类别表中没有 CatLevel 列 - cte Tree 会为您创建它。如果要经常使用查询，最好将其添加到表中（UPDATE代码在最后注释）。

如果您希望拥有动态列数（不像我的情况那样固定为 5），请为其创建动态 SQL 查询。

DECLARE @RootId INT, @SearchId INT
SET @RootId = 0 
SET @SearchId = 0

--SELECT it.Id, it.ParentId, it.Name, it.RootId FROM ItemTree it 
--WHERE (@RootId = 0 OR it.RootId = @RootId) AND (@SearchId = 0 OR it.Id = @SearchId)

;WITH Tree AS --gets category levels
(
  SELECT 1 as CatLevel, it.Id, it.ParentId, it.Name, it.RootId
  FROM ItemTree it WHERE it.ParentId IS NULL AND (@RootId = 0 OR it.RootId = @RootId) AND (@SearchId = 0 OR it.Id = @SearchId)

  UNION ALL

  SELECT t.CatLevel + 1 as CatLevel, it.Id, it.ParentId, it.Name, it.RootId
  FROM ItemTree it
  INNER JOIN Tree t ON it.ParentId = t.Id
  WHERE (@RootId = 0 OR it.RootId = @RootId) AND (@SearchId = 0 OR it.Id = @SearchId)

  ----if CatLevel already exists in the table, just use it instead for better performance
  --SELECT it.CatLevel, it.Id, it.ParentId, it.Name FROM ItemTree it 
  --WHERE (@RootId = 0 OR it.RootId = @RootId) AND (@SearchId = 0 OR it.Id = @SearchId)
)
, LeafIds AS --use it to show only leaf categories
(
  SELECT DISTINCT Id FROM ItemTree it WHERE (@RootId = 0 OR it.RootId = @RootId)
  EXCEPT
  SELECT DISTINCT ParentId FROM ItemTree it WHERE (@RootId = 0 OR it.RootId = @RootId)
)
, LeafTree AS --prepare data for pivots
(
  SELECT t.CatLevel, t.Id, t.ParentId, t.Name, Id AS LeafId
  FROM Tree t --WHERE Id IN (SELECT Id FROM LeafIds)

  UNION ALL

  SELECT t.CatLevel, t.Id, t.ParentId, t.Name, lt.LeafId
  FROM Tree t
  INNER JOIN LeafTree lt ON lt.ParentId = t.Id
)
, TreeFinal AS --make pivots for Id and Name over same leafIds
(
  SELECT t.*, pt1.[1] AS ID_1, pt1.[2] AS ID_2, pt1.[3] AS ID_3, pt1.[4] AS ID_4, pt1.[5] AS ID_5
  , pt2.[1] AS Name_1, pt2.[2] AS Name_2, pt2.[3] AS Name_3, pt2.[4] AS Name_4, pt2.[5] AS Name_5
  , ISNULL(pt2.[1], '') + ISNULL(' - ' + pt2.[2], '') + ISNULL(' - ' + pt2.[3], '') + ISNULL(' - ' + pt2.[4], '') + ISNULL(' - ' + pt2.[5], '')  AS Path
  FROM Tree t

  JOIN (
    SELECT LeafId, CatLevel, Id
    FROM LeafTree
  ) source
  PIVOT ( MAX(Id) FOR CatLevel IN ([1], [2], [3], [4], [5]) ) AS pt1 ON pt1.LeafId = t.Id

  JOIN (
    SELECT LeafId, CatLevel, Name
    FROM LeafTree
  ) source
  PIVOT ( MAX(Name) FOR CatLevel IN ([1], [2], [3], [4], [5]) ) AS pt2 ON pt2.LeafId = t.Id
) 
--Use following to update CatLevel in your table, if it was not there before
--UPDATE it SET CatLevel = Tree.CatLevel FROM Tree JOIN dbo.ItemTree it ON it.Id = Tree.Id
SELECT * FROM TreeFinal ORDER BY Path

这是查询前后的屏幕截图