SQL语法：在日期范围之间为每个日期选择结果

Question

我有以下SQL（SQL Server 2005），可用于计算每一列的总时间：

DECLARE @Param1 DATETIME
DECLARE @Param2 DATETIME
DECLARE @Param3 DATETIME

SET @Param1 = '2009-01-01'
SET @Param2 = '2009-09-09'

SELECT  SUM(IdleSec) AS TotalIdleSec, 
        SUM(ProductionSec) AS TotalProductionSec, 
        SUM(UplineSec) AS TotalUplineSec, 
        SUM(DownlineSec) AS TotalDownlineSec, 
        SUM(UserSec) AS TotalUserSec
FROM            Job
WHERE   (DateTime >= dbo.FormatDateTime(@Param1, 'yyyy-mm-dd')) 
        AND 
        (DateTime < dbo.FormatDateTime(@Param2, 'yyyy-mm-dd'))

GO

这将为上面的项目返回一个包含 1行 的表格，并且效果很好。

我有点不确定如何返回每天/每周/每月的SUM值表，即

• 日期范围之间每天的所有值的总和。

• 日期范围内每个WEEK的所有值的总和。

• 日期范围之间每个MONTH的所有值的总和。

我敢肯定有一个简单的方法可以做到这一点，但我不确定。我看过一些使用DAY（date）命令的教程，但我尝试过，似乎无法获得所需的东西。

我期待着您的出色帮助。

Answer 1

您将GROUP BY子句添加到查询中：

按MONTH：

SELECT  SUM(IdleSec) AS TotalIdleSec, 
        SUM(ProductionSec) AS TotalProductionSec, 
        SUM(UplineSec) AS TotalUplineSec, 
        SUM(DownlineSec) AS TotalDownlineSec, 
        SUM(UserSec) AS TotalUserSec,
        DATEPART(year, DateTime) as Year,
        DATEPART(month, DateTime) as Month
FROM            Job
WHERE   (DateTime >= dbo.FormatDateTime(@Param1, 'yyyy-mm-dd')) 
        AND 
        (DateTime < dbo.FormatDateTime(@Param2, 'yyyy-mm-dd'))
GROUP BY DATEPART(year, DateTime),
        DATEPART(month, DateTime);

通过周：

SELECT  SUM(IdleSec) AS TotalIdleSec, 
        SUM(ProductionSec) AS TotalProductionSec, 
        SUM(UplineSec) AS TotalUplineSec, 
        SUM(DownlineSec) AS TotalDownlineSec, 
        SUM(UserSec) AS TotalUserSec,
        DATEPART(year, DateTime) as Year,
        DATEPART(week, DateTime) as Week
FROM            Job
WHERE   (DateTime >= dbo.FormatDateTime(@Param1, 'yyyy-mm-dd')) 
        AND 
        (DateTime < dbo.FormatDateTime(@Param2, 'yyyy-mm-dd'))
GROUP BY DATEPART(year, DateTime),
        DATEPART(week, DateTime);

到DAY：

 SELECT  SUM(IdleSec) AS TotalIdleSec, 
        SUM(ProductionSec) AS TotalProductionSec, 
        SUM(UplineSec) AS TotalUplineSec, 
        SUM(DownlineSec) AS TotalDownlineSec, 
        SUM(UserSec) AS TotalUserSec,
        DATEPART(year, DateTime) as Year,
        DATEPART(dayofyar, DateTime) as Day
FROM            Job
WHERE   (DateTime >= dbo.FormatDateTime(@Param1, 'yyyy-mm-dd')) 
        AND 
        (DateTime < dbo.FormatDateTime(@Param2, 'yyyy-mm-dd'))
GROUP BY DATEPART(year, DateTime),
        DATEPART(dayofyear, DateTime);

对于DAY，还有其他选项，例如使用CAST提取日期部分或您似乎拥有的FormatDateTime函数。我使用DATEPART来保持一致性。