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

Question

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

sql-servert-sqlrecursioncommon-table-expression

3

好的，这是我要做的事情。我正在使用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个结果称为“心脏病学”，但它显示0个产品在这个类别下面，有子节点（您之前看到的）包含产品。我的一个大问题是，在这个级别上（父级942），如何使它计算下面子节点中包含的产品数量，以显示13作为“ProductCount”。我有点想需要一个递归方法。我尝试过了，但没有成功。

我可以接受一个存储过程来完成我所寻找的内容。我没有固定在一种特定的方式上，所以任何其他建议都会被欢迎。

- user123888

2个回答

2

您的WHERE语句将结果限制为一个父级。如果您想查看所有942以下的子项，请在CTE中指定942作为根。例如：

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

顺便问一下，分类可以有多个父级吗？如果不行的话，考虑删除Categories_XREF表并将ParentID存储在Categories表中。

- Andomar

Andomar，是的，可能会有多个父级，否则我会按照你建议的方式去做。 - user123888

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- AakashM · Accepted Answer

编辑好的，实际上阅读了要求并思考了一下，这其实相当容易（我认为！）

关键是我们想要两件事：类别层次结构和产品数量。层次结构通过递归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

我要感谢无价的Itzik Ben-Gan所写的这篇文章，启发了我的思考。他的书《Inside MS SQL Server 2005：T-SQL查询》强烈推荐。