递归查询 - 仅选择叶节点代表活动数据的节点

Question

给定以下递归查询：

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 级，因此我不确定如何修改查询以确保包含这些节点。

谢谢， 凯尔

Answer 1

您可以创建一个跟踪层次结构的路径列。然后，您只能添加在 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) 

Answer 2

如果我理解正确的话，您希望所有节点都比叶级别高一级？

您实际上不需要为此进行递归查询。您所要做的就是首先找到叶节点，然后选择所有父节点。

WITH LeafNodeParents AS
(
    SELECT DISTINCT ParentId
    FROM StoreDepartment
    WHERE DepartmentId NOT IN
    (
        SELECT DISTINCT ParentId FROM StoreDepartment
    )
)
SELECT d.DepartmentId, d.Name, d.IsInactive, d.IsSpecial, d.ParentId
FROM LeafNodeParents p
INNER JOIN StoreDepartment d
    ON d.DepartmentId = p.ParentId

唯一不能告诉你的是水平。我不确定你有多需要它。如果不这样做，这应该比递归版本表现得更好；如果你这样做，看起来 Jose 的查询就可以了（快速浏览一下即可判断）。