在 SQL Server 2008r2 中使用自连接表聚合数据

Question

我试图在典型的餐厅类型数据库上提供报告功能。我在下面描述了问题的具体情况，但简而言之，我需要能够检索与分层自连接“类别”表相关的项目的聚合数据（总和和计数）。我知道这很冗长而且可能令人困惑，所以我将尝试用一个例子来传达细节。我有四个专门针对此问题的表：

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

此报告功能的目标是获取类别 ID 并返回给定时间段内的总和（或计数）。如果传入的类别是叶类别（例如瓶装啤酒），那么我可以毫无问题地计算这个总数。然而，如果它的层次结构较高，例如“食品”，我无法弄清楚如何以汇总所有子类别的方式编写查询。

我正在使用 SQL Server 2008r2。我尝试使用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 美元组成，而啤酒的 6.50 美元又分别由生啤酒和瓶装啤酒的 3.50 美元和 3.00 美元组成......等等等等......
这与以下层次结构相匹配类别。

代码如下，这应该适用于您的架构。如果您希望限制输出，请在紧靠分组依据之前添加一个 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

Answer 2

您是否尝试过使用递归 CTE 来确定感兴趣的类别 ID，然后在进行聚合时加入这些结果？我想是这样的：

WITH CatR AS
(
    SELECT Id FROM Categories WHERE Id = @CategoryId
    UNION ALL
    SELECT Categories.Id 
    FROM Cat
        INNER JOIN Categories ON Categories.ParentId = Cat.Id
)
SELECT SUM(Price)
FROM Orders
    INNER JOIN OrderItems ON OrderItems.OrderId = Orders.Id
    INNER JOIN MenuItems ON MenuItems.Id = OrderItems.MenuItemId
    INNER JOIN CatR ON CatR.Id = MenuItems.CategoryId
WHERE Orders.DateCompleted BETWEEN @StartDate AND @EndDate

Answer 3

我会更改数据库。问题是同一个表中有两种不同类别的项目。

将食物和饮料放入 CategoryType 表中。
将其余部分保留在“类别”表中。
加入他们吧。

或者，为了避免使用另一个表，请将类别类型字段添加到类别表中。类型可以是食物或饮料。您可以使用“食物”和“饮料”或“F”和“D”。纯粹主义者可能不喜欢这样，但优点是它更简单并且运行速度更快。