SQL 语法:选择日期范围内每个日期的结果

2021-09-10 00:00:00 sql tsql sql-server

我使用以下 SQL (SQL Server 2005) 来计算每列的总时间:

I have the following SQL (SQL Server 2005) that I use to calculate the total times for each column:

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 行的表格，用于上述项目并且效果很好.

This returns a table with 1 row for the items above and works great.

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

I am a little uncertain how I can return the table with the SUM values per day/week/month i.e.

  • 日期范围内每一天的所有值的总和.

  • Sum of all values for each DAY between the range of dates.

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

Sum of all values for each WEEK between the range of dates.

日期范围内每个 MONTH 的所有值的总和.

Sum of all values for each MONTH between the range of dates.

我确信有一种简单的方法可以做到这一点，但我自己不确定.我看过一些使用 DAY(date) 命令的教程，但我尝试过但似乎无法得到我需要的东西.

I am sure there is a simple way to do this but uncertain myself. I have seen some tutorials that use the DAY(date) command but I tried and cannot seem to get what I need.

我期待着您的出色帮助.

I look forward to your excellent help.

推荐答案

您在查询中添加 GROUP BY 子句:

You add a GROUP BY clause to your query:

按月:

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

按天:

 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.

For DAY there are other options like using CAST to extract the day part or the FormatDateTime function you seem to have. I used DATEPART for consistency.

相关文章