SQL 按天分组，显示每天的订单
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/329477/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL group by day, show orders for each day
提问by Radu094
I have an SQL 2005 table, let's call it Orders, in the format:
我有一个 SQL 2005 表，我们称之为订单，格式如下：
OrderID, OrderDate, OrderAmount
1, 25/11/2008, 10
2, 25/11/2008, 2
3, 30/1002008, 5
Then I need to produce a report table showing the ordered amount on each day in the last 7 days:
然后我需要生成一个报告表，显示过去 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
The SQL query that would normally produce this:
通常会产生这样的 SQL 查询：
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.
通常我会使用一个计数表和外部连接来解决这个问题，但我真的在寻找一个更简单或更有效的解决方案。对于报告查询来说，这似乎是一个常见的需求，应该已经有一些优雅的解决方案可用了。
So: 1. Can this result be obtain from a simple query without using tally tables?
所以： 1. 这个结果可以从一个简单的查询中获得而不使用计数表吗？
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)?
和 2. 如果不是，我们能否（可靠地）即时创建这个计数表（我可以使用 CTE 创建一个计数表，但递归堆栈将我限制为 100 行）？
回答by Timothy Khouri
SQL isn't "skipping" dates... because queries run against datathat 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 :)
SQL 不会“跳过”日期……因为查询是针对实际存在于表中的数据运行的。因此，如果您在表中没有 1 月 14 日的 DATE，那么为什么 SQL 会向您显示结果:)
What you need to do is make a temp table, and JOIN to it.
您需要做的是制作一个临时表，然后加入它。
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)
There you go!
你去吧！
回答by C73
I had the same problem and this is how I solved it:
我遇到了同样的问题，这就是我解决它的方法：
SELECT datename(DW,nDays) TimelineDays,
Convert(varchar(10), nDays, 101) TimelineDate,
ISNULL(SUM(Counter),0) Totals
FROM (Select GETDATE() AS nDays
union Select GETDATE()-1
union Select GETDATE()-2
union Select GETDATE()-3
union Select GETDATE()-4
union Select GETDATE()-5
union Select GETDATE()-6) AS tDays
Left Join (Select * From tHistory Where Account = 1000) AS History
on (DATEPART(year,nDays) + DATEPART(MONTH,nDays) + DATEPART(day,nDays)) =
(DATEPART(year,RecordDate) + DATEPART(MONTH,RecordDate) + DATEPART(day,RecordDate))
GROUP BY nDays
ORDER BY nDays DESC
The ouput is:
输出是：
TimelineDays, TimelineDate, Totals
Tuesday 10/26/2010 0
Monday 10/25/2010 6
Sunday 10/24/2010 3
Saturday 10/23/2010 2
Friday 10/22/2010 0
Thursday 10/21/2010 0
Wednesday 10/20/2010 0
回答by user427265
If you want to see value zero than put the following query:
如果您想查看零值，请执行以下查询：
select count(*), sum(OrderAmount)
from Orders
where OrderDate>getdate()-7
and sum(OrderAmount) > 0 or sum(OrderAmount) = 0
group by datepart(day,OrderDate)
回答by Suvabrata Roy
CREATE PROCEDURE [dbo].[sp_Myforeach_Date]
-- Add the parameters for the stored procedure here
@SatrtDate as DateTime,
@EndDate as dateTime,
@DatePart as varchar(2),
@OutPutFormat as int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
Declare @DateList Table
(Date varchar(50))
WHILE @SatrtDate<= @EndDate
BEGIN
INSERT @DateList (Date) values(Convert(varchar,@SatrtDate,@OutPutFormat))
IF Upper(@DatePart)='DD'
SET @SatrtDate= DateAdd(dd,1,@SatrtDate)
IF Upper(@DatePart)='MM'
SET @SatrtDate= DateAdd(mm,1,@SatrtDate)
IF Upper(@DatePart)='YY'
SET @SatrtDate= DateAdd(yy,1,@SatrtDate)
END
SELECT * FROM @DateList
END
Just put this Code and call the SP in This way
只需放置此代码并以这种方式调用SP
exec sp_Myforeach_Date @SatrtDate='03 Jan 2010',@EndDate='03 Mar 2010',@DatePart='dd',@OutPutFormat=106
Thanks *Suvabrata RoyICRA Online Ltd. Kolkata*
谢谢 * Suvabrata RoyICRA Online Ltd. Kolkata*
回答by HLGEM
Since you will want to use this date table frequently in other queries as well, I suggest you make it a permanent table and create a job to add the new year's dates once a year.
由于您也希望在其他查询中经常使用此日期表，因此我建议您将其设为永久表并创建一个作业来每年添加一次新年日期。
回答by Jonathan Leffler
Depending on how SQL Server handles temporary tables, you can more or less easily arrange to create a temporary table and populate it with the 7 (or was that 8?) dates you are interested in. You can then use that as your tally table. There isn't a cleaner way that I know of; you can only select data that exists in a table or that can be derived from data that exists in a table or set of tables. If there are dates not represented in the Orders table, you can't select those dates from the Orders table.
根据 SQL Server 处理临时表的方式，您可以或多或少地轻松安排创建一个临时表并用您感兴趣的 7 个（或者是 8 个？）日期填充它。然后您可以将其用作您的计数表。据我所知，没有一种更干净的方法；您只能选择存在于表中的数据，或者可以从存在于表或表集中的数据中派生的数据。如果 Orders 表中没有显示日期，则无法从 Orders 表中选择这些日期。
