Rekursive Abfrage - Nur Knoten auswählen, deren Blattknoten aktive Daten darstellen

Question

Gegeben sei die folgende rekursive Abfrage:

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 

Ich kann Daten auswählen, die wie folgt aussehen:

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

Nehmen Sie außerdem an, dass eine Tabelle mit DepartmentId und ItemId existiert (z. B. DepartmentItemRelationship). Blattknoten aus der Abteilungshierarchie werden hier mit Artikeln gepaart.

Ich möchte, dass meine rekursive Abfrage nur Knoten (auf jeder Ebene) zurückgibt, die mindestens einen Blattknoten unter ihnen mit einer Übereinstimmung in der Abteilung/Artikel-Beziehungstabelle haben. Diese Knoten könnten 6 oder 7 Ebenen tiefer liegen, so dass ich nicht sicher bin, wie ich meine Abfrage ändern würde, um sicher zu sein, dass sie diese einschließt.

Danke! Kyle

Answer 1

Sie können eine Pfadspalte erstellen, in der Sie die Hierarchie verfolgen können. Dann können Sie nur die untergeordneten Knoten hinzufügen, die eine Übereinstimmung in der Tabelle DepartmentItemRelationship aufweisen. Und schließlich erhalten Sie nur die Knoten, die mindestens ein Kind haben.

Versuchen Sie so etwas:

    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

Wenn ich Sie richtig verstehe, wollen Sie alle Knoten, die genau eine Ebene über der Blattebene liegen?

Eine rekursive Abfrage ist dafür eigentlich nicht erforderlich. Sie müssen nur zuerst die Blattknoten finden und dann alle übergeordneten Knoten auswählen.

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

Das Einzige, was Sie damit nicht erfahren, ist das Niveau. Ich bin nicht sicher, wie dringend Sie das brauchen. Wenn nicht, sollte dies viel besser sein als die rekursive Version; wenn ja, sieht es so aus, als ob die Abfrage von Jose dafür geeignet ist (nach einem kurzen Blick zu urteilen).