sql - 使用SQL Server 2008r2中的自联接表聚合数据

Question

我正在尝试在典型的餐厅类型数据库上提供报告功能。我将在下面描述问题的具体细节，但总而言之，我需要能够检索与分层自联接“类别”表相关的项目的汇总数据（总和和计数）。我知道这很罗and，而且可能令人困惑，因此，我将尝试通过一个示例来传达细节。我有四个特定于此问题的表：

Categories
Id    Name          ParentId
1     Food          NULL
2     Drinks        NULL
3     Beer          2
4     Draft Beer    3
5     Bottle Beer   4
6     Pizza         1
8     Sandwiches    1

ParentId是FK回到分类

MenuItems
Id    Name              CategoryId  
1     6" Sausage        6
2     French Dip        8
3     Dogfish 60 IPA    4
4     Dogfish 60 IPA    5
5     Bud Light         5

Orders
Id    Total   DateReceived    DateCompleted
1     12.00   1/1/1970 1:00   1/1/1970 1:05 
2     11.50   1/1/1970 1:08   1/1/1970 1:18

OrderItems
Id    Price   OrderId   MenuItemId
1     9.00    1         1
2     3.00    1         5
3     3.50    2         3
4     8.00    2         2

此报告功能的目标是采用categoryId并返回给定时间段内的总和（或计数）。如果输入类别是叶子类别（例如，瓶装啤酒），则计算此总数没有问题。但是，如果它在层次结构（例如“食物”）中处于较高位置，则我无法弄清楚如何编写查询以汇总所有子类别。

我正在使用SQL Server 2008r2。我尝试使用WITH WITH common_table_expression查询，但是出现了有关“递归cte的递归部分”中不允许的聚合函数的错误。

我今天使用的SQL看起来像这样：

SELECT SUM(oi.Price) as Total, DAY(o.Completed)
FROM Orders o
JOIN OrderItems oi on oi.orderId = o.id
JOIN MenuItems mi on oi.MenuItemId = mi.id
JOIN Categories c on mi.CategoryId = c.id
WHERE o.Completed is not null
AND c.Id = @categoryId
AND o.Completed between @startDate and @endDate
GROUP BY DAY(o.completed)

同样，如果@categoryId是叶类别，则此查询将为我提供确切的信息。如果不是，则不返回任何内容。我希望能够提供@ category = 2（饮料）并获取“饮料”类别层次结构中任何位置的所有项目的总和。

谢谢！

Answer 1

托比
实际上，您尝试通过CTE递归方法解决此问题非常接近。递归在大多数情况下是令人困惑的，但它是解决此类问题的最佳解决方案。
我已经复制了您在问题中指定的架构，并提出了以下解决方案。我已经对其进行了测试，并产生以下输出...我假设这就是您想要看到的...

CategoryName CategoryId TotalSaleAmount
Food               1    17.00
Drinks             2     6.50
Beer               3     6.50
Draft Beer         4     3.50
Bottle Beer        5     3.00
Pizza              6     9.00
Sandwiches         8     8.00

显然，这17美元的食物是由比萨饼和三明治组成的。同样，饮料的6.50美元由啤酒的6.50美元构成，而啤酒的价格分别由生啤酒和瓶装啤酒的3.50美元和3.00美元组成……等等。这与类别的层次结构相匹配。

下面的代码，这应该与您的架构一起使用。如果要限制输出，请在group by之前添加一个where子句。

WITH CategorySearch(ParentId, Id) AS
(
SELECT ID AS ParentId, ID
FROM Categories
WHERE ID NOT IN (SELECT ParentId FROM Categories 
                WHERE ParentId IS NOT NULL)
UNION ALL
SELECT Categories.ParentId, CS.Id
    FROM Categories Categories INNER JOIN CategorySearch CS
    ON Categories.Id = CS.ParentId
    WHERE Categories.ParentId IS NOT NULL
)
SELECT CA.Name AS CategoryName, CS.ParentId AS CategoryID, SUM(OI.Price) AS TotalSaleAmount
FROM Categories CA INNER JOIN CategorySearch CS
    ON CS.ParentId = CA.ID
INNER JOIN MenuItems MI
    ON MI.CategoryId = CS.Id
INNER JOIN OrderItems OI
    ON OI.MenuItemId = MI.ID
INNER JOIN Orders O
    ON OI.OrderId = O.Id
GROUP BY CA.Name, CS.ParentId
ORDER BY CS.ParentId