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

Question

我正在尝试在一个典型的餐厅类型数据库中提供报告功能。我会在下面描述问题的具体细节，但简而言之，我需要能够检索与分层自连接“Category”表相关的项目的聚合数据（总和和计数）。我知道这很啰嗦，可能很令人困惑，因此我将尝试用一个例子来传达细节。我有四个特定于此问题的表：

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 是指向 Categories 的外键

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 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（饮料），并获得“饮料”类别层次结构中任何地方的所有项目的总和。

谢谢！

- tobyb

Answer 1

Tobyb,
你使用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

- Scott Barron

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

- Michael Petito

---

谢谢回复！我已经接近这个结果了，但还没有完全弄对。我将尝试一下。 - tobyb

Answer 3

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

将食物和饮料放入一个CategoryType表中。 其余的留在Category表中。 连接它们。

或者，为了避免再添加一个表，可以在Category表中添加一个类别类型字段。类型可以是食物或饮料。你可以使用"Food"和"Drink"或'F'和'D'。纯粹主义者可能不喜欢这样做，但优点是它更简单，运行速度更快。

- Steve Wellens