sql - 递归查询-仅选择叶节点表示事件数据的节点

给定以下递归查询:

WITH DepartmentHierarchy (DepartmentID, Name, IsInactive, IsSpecial, ParentId, HierarchyLevel) AS
(
   -- Base case
   SELECT
      DepartmentId,
      Name,
      IsInactive,
      IsSpecial,
      ParentId,
      1 as HierarchyLevel
   FROM StoreDepartment
   WHERE ParentId IS NULL

   UNION ALL

   -- Recursive step
   SELECT
      d.DepartmentId,
       d.Name,
      d.IsInactive,
      d.IsSpecial,
      d.ParentId,
      dh.HierarchyLevel + 1 AS HierarchyLevel
   FROM StoreDepartment d
      INNER JOIN DepartmentHierarchy dh ON
         d.ParentId = dh.DepartmentId
) SELECT * FROM DepartmentHierarchy

我可以选择看起来像这样的数据:

DepartmentId, Name, IsInactive, IsSpecial, ParentId, HeirarchyLevel
1, Store, 0, 0, NULL, 1
2, Main Department 1, 0, 1, 2
3, Main Department 2, 0, 1, 2
4, Sub For Main 1, 0, 2, 3

此外，假设存在一个表，其中包含DepartmentId和ItemId(例如:DepartmentItemRelationship)。部门层次结构中的叶节点在此处与项目配对。

我希望我的递归查询仅返回在其下面至少具有一个叶节点且部门/项目关系表中具有匹配项的节点(任何级别)。这些节点可能会下降6或7级，因此我不确定如何修改查询以确保包括这些节点。

谢谢，
凯尔

最佳答案

您可以创建一个跟踪列的路径列。然后，您只能在DepartmentItemRelationship表中添加具有匹配项的子节点。最后，仅获取至少有一个子节点。

尝试这样的事情:

    WITH DepartmentHierarchy (DepartmentID, Name, IsInactive, IsSpecial, ParentId, HierarchyLevel) AS
(
   -- Base case
   SELECT
      '/'+cast( DepartmentId as varchar(max)) as [path]
      DepartmentId,
      Name,
      IsInactive,
      IsSpecial,
      ParentId,
      1 as HierarchyLevel
   FROM StoreDepartment
   WHERE ParentId IS NULL

   UNION ALL

   -- Recursive step
   SELECT
      dh.[path] +'/'+ cast( d.DepartmentId as varchar(max)) as [path]
      d.DepartmentId,
      d.Name,
      d.IsInactive,
      d.IsSpecial,
      d.ParentId,
      dh.HierarchyLevel + 1 AS HierarchyLevel
   FROM StoreDepartment d
      INNER JOIN DepartmentHierarchy dh ON
         d.ParentId = dh.DepartmentId
   where exists ( select top 1 1
                  from DepartmentItemRelationship di
                  where di.DepartmentId = d.DepartmentId )
)
SELECT *
FROM DepartmentHierarchy dh
where exists ( select top 1 1
               from DepartmentHierarchy
               where charindex('/'+dh.DepartmentID+'/',[path]) > 0)

关于sql - 递归查询-仅选择叶节点表示事件数据的节点，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2367124/