3 Stimmen

Rekursive Abfrage mit CTE in SQL Server 2005

OK, ich versuche Folgendes. Ich verwende eine CTE-Abfrage in MSSQL2005. Das Ziel der Abfrage ist es, durch Eltern-Kind-Beziehungen von Produktkategorien zu rekursieren und die Anzahl der Produkte unter jeder Kategorie zurückzugeben (dies schließt alle Produkte ein, die in untergeordneten Kategorien enthalten sind)

Meine aktuelle Version gibt nur die Anzahl der Produkte für die angezeigte Kategorie zurück. Sie berücksichtigt keine Produkte, die in einer der untergeordneten Kategorien enthalten sein können.

Nachfolgend finden Sie den Datenbank-Dump, mit dem ich das Problem reproduzieren kann, sowie die von mir verwendete Abfrage und eine Erklärung:

    CREATE TABLE [Categories] (
   [CategoryID] INT,
   [Name] NCHAR(150)

    )
    GO

/* Data for the `Query_Result` table  (Records 1 - 5) */

INSERT INTO [Categories] ([CategoryID], [Name])
VALUES (942, N'Diagnostic Equipment')
GO

INSERT INTO [Categories] ([CategoryID], [Name])
VALUES (943, N'Cardiology')
GO

INSERT INTO [Categories] ([CategoryID], [Name])
VALUES (959, N'Electrodes')
GO

INSERT INTO [Categories] ([CategoryID], [Name])
VALUES (960, N'Stress Systems')
GO

INSERT INTO [Categories] ([CategoryID], [Name])
VALUES (961, N'EKG Machines')
GO

CREATE TABLE [Categories_XREF] (
   [CatXRefID] INT,
   [CategoryID] INT,
   [ParentID] INT
)
GO

/* Data for the `Query_Result` table  (Records 1 - 5) */

INSERT INTO [Categories_XREF] ([CatXRefID], [CategoryID], [ParentID])
VALUES (827, 942, 0)
GO

INSERT INTO [Categories_XREF] ([CatXRefID], [CategoryID], [ParentID])
VALUES (828, 943, 942)
GO

INSERT INTO [Categories_XREF] ([CatXRefID], [CategoryID], [ParentID])
VALUES (928, 959, 943)
GO

INSERT INTO [Categories_XREF] ([CatXRefID], [CategoryID], [ParentID])
VALUES (929, 960, 943)
GO

INSERT INTO [Categories_XREF] ([CatXRefID], [CategoryID], [ParentID])
VALUES (930, 961, 943)
GO

CREATE TABLE [Products_Categories_XREF] (
   [ID] INT,
   [ProductID] INT,
   [CategoryID] INT
)
GO

/* Data for the `Query_Result` table  (Records 1 - 13) */

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252065, 12684, 961)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252066, 12685, 959)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252067, 12686, 960)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252068, 12687, 961)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252128, 12738, 961)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252129, 12739, 959)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252130, 12740, 959)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252131, 12741, 959)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252132, 12742, 959)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252133, 12743, 959)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252134, 12744, 959)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252135, 12745, 959)
GO

INSERT INTO [Products_Categories_XREF] ([ID], [ProductID], [CategoryID])
VALUES (252136, 12746, 959)
GO

CREATE TABLE [Products] (
   [ProductID] INT
)
GO

/* Data for the `Query_Result` table  (Records 1 - 13) */

INSERT INTO [Products] ([ProductID])
VALUES (12684)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12685)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12686)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12687)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12738)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12739)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12740)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12741)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12742)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12743)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12744)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12745)
GO

INSERT INTO [Products] ([ProductID])
VALUES (12746)
GO

Hier ist die CTE-Abfrage, die ich verwendet habe:

WITH ProductCategories (CategoryID, ParentID, [Name], Level)
AS
(
-- Anchor member definition
   SELECT
   C.CategoryID,
   CXR.ParentID,
   C.Name,
   0 AS Level
  FROM
  Categories C,
  Categories_XRef CXR
  WHERE
  C.CategoryID = CXR.CategoryID
  AND CXR.ParentID = 0
  UNION ALL
-- Recursive member definition
SELECT
   C.CategoryID,
   CXR.ParentID,
   C.Name,
   Level + 1
  FROM
  Categories C,
  Categories_XRef CXR,
  ProductCategories AS PC
  WHERE
  C.CategoryID = CXR.CategoryID 
  AND CXR.ParentID = PC.CategoryID

)
SELECT 
    PC.ParentID, 
    PC.CategoryID, 
    PC.Name, 
    PC.Level,
    (SELECT 
        Count(P.ProductID) 
     FROM 
        Products P,
        Products_Categories_XREF PCXR 
      WHERE 
       P.ProductID = PCXR.ProductID
       AND PCXR.CategoryID = PC.CategoryID
      ) as ProductCount
FROM     
    Categories C,
    ProductCategories  PC
WHERE
 PC.CategoryID = C.CategoryID
 AND PC.ParentID = 943
ORDER BY 
    Level, PC.Name

Ändern Sie zunächst die "PC.ParentID" auf 943. Es werden drei Datensätze zurückgegeben, die die Produktanzahl für jede angezeigte Kategorie zeigen.

Ändern Sie nun die ParentID von 943 zu 942 und führen Sie es erneut aus. Sie sehen nun 1 Ergebnis mit der Bezeichnung "Kardiologie", aber es zeigt 0 Produkte Unter dieser Kategorie gibt es Kinder (die Sie zuvor gesehen haben), die Produkte enthalten. Meine große Frage ist, wie kann ich auf dieser Ebene (Parent 942) die Produkte zählen lassen, die in den Unterkategorien enthalten sind, um 13 als "ProductCount" anzuzeigen? Ich denke, dass ich vielleicht eine weitere Rekursionsmethode benötige. Ich habe das schon versucht, hatte aber keinen Erfolg.

Ich bin offen für eine gespeicherte Prozedur, die tun würde, was ich suche. Ich bin nicht auf einen bestimmten Weg festgelegt. Andere Vorschläge wären also sehr willkommen.

4voto

AakashM Punkte 60642

bearbeiten OK, nachdem ich die Anforderungen gelesen und ein wenig nachgedacht habe, ist das eigentlich ganz einfach (denke ich!)

Der Punkt ist, dass wir zwei Dinge wollen: die Kategorienhierarchie und eine Zählung der Produkte. Die Hierarchie wird durch ein rekursives CTE erstellt, und die Zählung erfolgt außerhalb dieses CTE:

-- The CTE returns the cat hierarchy:
-- one row for each ancestor-descendant relationship
-- (including the self-relationship for each category)
WITH CategoryHierarchy AS (
    -- Anchor member: self relationship for each category
    SELECT CategoryID AS Ancestor, CategoryID AS Descendant
    FROM Categories
UNION ALL
    -- Recursive member: for each row, select the children
    SELECT ParentCategory.Ancestor, Children.CategoryID
    FROM 
        CategoryHierarchy AS ParentCategory
        INNER JOIN Categories_XREF AS Children
        ON ParentCategory.Descendant = Children.ParentID
)
SELECT CH.Ancestor, COUNT(ProductID) AS ProductsInTree
-- outer join to product-categories to include 
-- all categories, even those with no products directly associated
FROM CategoryHierarchy CH
LEFT JOIN Products_Categories_XREF PC
ON CH.Descendant = PC.CategoryID
GROUP BY CH.Ancestor

Die Ergebnisse sind:

Ancestor    ProductsInTree
----------- --------------
942         13
943         13
959         9
960         1
961         3

Ich stehe in der Schuld von dieser Artikel von dem unschätzbaren Itzik Ben-Gan dafür, dass er mein Denken angeregt hat. Sein Buch "Inside MS SQL Server 2005: T-SQL Querying" ist sehr empfehlenswert.

0 Stimmen

Nach Glättung einiger anderer Fehler bleibt dieser grundlegende Fehler übrig: GROUP BY, HAVING oder Aggregatfunktionen sind im rekursiven Teil eines rekursiven gemeinsamen Tabellenausdrucks 'ProductCategories' nicht erlaubt.

0 Stimmen

Andomar, auch ich habe diese Fehlermeldung erhalten, als ich das Gleiche versuchte. Offenbar kann man in einer rekursiven Abfrage nur einfache Selects verwenden.

0 Stimmen

AakashM - dies funktioniert perfekt, obwohl die Frage ist - wie die Ergebnisse der Abfrage auf alles unter 943 zu begrenzen? Das Hinzufügen der where-Klausel, wie in der nächsten Antwort vorgeschlagen, funktioniert nicht ganz.

2voto

Andomar Punkte 224164

Ihre WHERE-Anweisung beschränkt das Ergebnis auf ein Elternteil. Wenn Sie alle Kinder unter 942 sehen möchten, geben Sie 942 als Wurzel in der CTE an. Zum Beispiel:

WITH CTE (CategoryID, ParentID, [Name], [Level])
AS
(
  SELECT C.CategoryID, CXR.ParentID, C.Name, 0 AS Level
  FROM Categories C
  INNER JOIN Categories_XRef CXR ON C.CategoryID = CXR.CategoryID 
  WHERE CXR.CategoryID = 943
  UNION ALL
  SELECT C.CategoryID, CXR.ParentID, C.Name, Level + 1
  FROM Categories C
  INNER JOIN Categories_XRef CXR ON C.CategoryID = CXR.CategoryID 
  INNER JOIN CTE PC ON PC.CategoryID = CXR.ParentID
)
SELECT * FROM CTE

Übrigens, können Kategorien mehrere Eltern haben? Wenn nicht, sollten Sie erwägen, die Tabelle Categories_XREF zu eliminieren und ParentID in der Tabelle Categories zu speichern.

0 Stimmen

Andomar, ja, es kann mehrere Eltern geben, sonst würde ich genau das tun, was du vorgeschlagen hast.

CodeJaeger.com

CodeJaeger ist eine Gemeinschaft für Programmierer, die täglich Hilfe erhalten..
Wir haben viele Inhalte, und Sie können auch Ihre eigenen Fragen stellen oder die Fragen anderer Leute lösen.

Powered by:

X