sql-server - 在 SQL Server 2005 中使用 CTE 进行递归查询

Question

好的，这就是我想要做的。我在 MSSQL2005 中使用 CTE 查询。查询的目的是通过产品类别的父子关系递归并返回每个类别下的产品数量(这包括子类别中包含的任何产品)

我当前的版本只返回所显示类别的产品数量。它不考虑其任何子项中可能包含的产品。

重现问题的数据库转储，以及我使用的查询和解释如下:

    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

这是我使用的 CTE 查询:

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

首先，将“PC.ParentID”更改为 943。您将看到返回的三个记录显示每个类别的产品计数。

现在，将 ParentID 从 943 更改为 942 并重新运行。您现在会看到返回 1 个名为“Cardiology”的结果，但它显示 0 个产品在这个类别下，有包含产品的子项(您之前看到的)。我的大问题是，在这个级别(Parent 942)，我怎样才能让它计算下面子项中包含的产品以显示 13 作为“ProductCount”我有点想我可能需要一种递归方法。我试过了，但没有成功。

我愿意使用可以满足我要求的存储过程。我不喜欢一种特定的方式。因此，我们将不胜感激任何其他建议。

Answer 1

编辑 好的，实际上阅读了要求并思考了一下这实际上很容易(我认为!)

重点是我们需要两件事:类别层次结构和产品数量。层次结构由递归 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

结果是:

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

我很感激this article by the inestimable Itzik Ben-Gan 让我开始思考。强烈推荐他的书“Inside MS SQL Server 2005: T-SQL Querying”。