我正在尝试在一个典型的餐厅类型数据库中提供报告功能。我会在下面描述问题的具体细节,但简而言之,我需要能够检索与分层自连接“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(饮料),并获得“饮料”类别层次结构中任何地方的所有项目的总和。
谢谢!