SQL 按天分组，显示每天的订单

Question

我有一个 SQL 2005 表，我们称之为订单，格式如下:

OrderID, OrderDate,  OrderAmount
1,       25/11/2008, 10
2,       25/11/2008, 2
3,       30/1002008, 5

然后我需要生成一个报告表，显示过去 7 天内每天的订购数量:

Day,        OrderCount, OrderAmount
25/11/2008, 2,          12
26/11/2008, 0,          0 
27/11/2008, 0,          0 
28/11/2008, 0,          0
29/11/2008, 0,          0
30/11/2008, 1,          5

通常会产生这样的 SQL 查询:

select count(*), sum(OrderAmount)
    from Orders
    where OrderDate>getdate()-7
    group by datepart(day,OrderDate)

有一个问题，它会跳过没有订单的日子:

Day,        OrderCount, OrderAmount
25/11/2008, 2,          12
30/11/2008, 1,          5

通常我会使用一个计数表和外部连接来解决这个问题，但我真的在寻找一个更简单或更有效的解决方案。对于报告查询来说，这似乎是一个常见的需求，应该已经有一些优雅的解决方案可用了。

所以: 1. 这个结果可以从一个简单的查询中获得而不使用计数表吗？

和 2. 如果不是，我们能否(可靠地)即时创建这个计数表(我可以使用 CTE 创建一个计数表，但递归堆栈将我限制为 100 行)？

Answer 1

SQL 不会“跳过”日期……因为查询是针对实际存在于表中的数据运行的。因此，如果您在表中没有 1 月 14 日的 DATE，那么为什么 SQL 会向您显示结果:)

您需要做的是制作一个临时表，然后加入它。

CREATE TABLE #MyDates ( TargetDate DATETIME )
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 0, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 1, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 2, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 3, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 4, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 5, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 6, 101))
INSERT INTO #MyDates VALUES CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE() - 7, 101))

SELECT CONVERT(VARCHAR, TargetDate, 101) AS Date, COUNT(*) AS OrderCount
FROM dbo.Orders INNER JOIN #MyDates ON Orders.Date = #MyDates.TargetDate
GROUP BY blah blah blah (you know the rest)

你去吧!