SQL group by day，显示每天的订单。

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

我有一个SQL 2005表，我们称它为Orders，格式如下:

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行)?

6 个解决方案

#1

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 :)

SQL不是“跳过”枣…因为查询是针对实际在表中的数据进行的。因此，如果表中没有1月14日的日期，那么为什么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!

你走吧!

#2

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

#3

If you want to see value zero than put the following query:

如果您想要看到值0，请输入以下查询:

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

#4

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表中选择这些日期。

#5

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.

由于您还希望在其他查询中经常使用这个日期表，所以我建议您将它设置为一个永久性表，并创建一个job，以便每年添加一次新年日期。

#6

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 Roy ICRA Online Ltd. Kolkata*

感谢Suvabrata Roy ICRA Online Ltd. Kolkata*。

#1