SQL按天分组，显示每天的订单 [英] SQL group by day, show orders for each day

问题描述

我有一个SQL 2005表，将其称为Orders，格式为：

I have an SQL 2005 table, let's call it Orders, in the format:

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

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

Then I need to produce a report table showing the ordered amount on each day in the last 7 days:

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查询：

The SQL query that would normally produce this:

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

有一个问题是它将跳过没有订单的日期：

Has a problem in that it will skip the days where there are no orders:

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

通常，我将使用表表和外部连接对那里的行进行修复，但是我确实在寻找一种更简单或更有效的解决方案。似乎非常需要报表查询，因此应该已经有一些优雅的解决方案。

Normally I would fix this using a tally table and outer join against rows there, but I'm really looking for a simpler or more efficient solution for this. It seems like such a common requirement for a report query that some elegant solution should be available for this already.

因此：1.可以从简单查询中获得此结果而不使用统计表吗？

So: 1. Can this result be obtain from a simple query without using tally tables?

和2如果没有，我们是否可以即时（可靠地）创建此理货表格（我可以使用CTE创建理货表格，但递归堆栈将我限制为100行）？

and 2. If no, can we create this tally table (reliably) on the fly (I can create a tally table using CTE but recursion stack limits me to 100 rows)?

推荐答案

SQL并不是跳过日期……因为查询是针对表中实际存在的 data 运行的。因此，如果您在1月14日的表中没有DATE，那么为什么SQL会向您显示结果：）

SQL isn't "skipping" dates... because queries run against data that is actually in the table. So, if you don't have a DATE in the table for January 14th, then why would SQL show you a result :)

您需要做的是

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)

去吧！

这篇关于SQL按天分组，显示每天的订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！