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

SQL Syntax: Select results for each date between a range of dates(SQL 语法:选择日期范围内每个日期的结果)
本文介绍了SQL 语法:选择日期范围内每个日期的结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我使用以下 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.

这篇关于SQL 语法:选择日期范围内每个日期的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Number of working days between two dates(两个日期之间的工作日数)
How do I use dateadd to get the first day of last year?(如何使用 dateadd 获取去年的第一天?)
SQL- Count occurrences of a specific word within all stored procedures(SQL- 计算所有存储过程中特定单词的出现次数)
SQL query to make a column of numbers a string(使一列数字成为字符串的 SQL 查询)
T-SQL: Best way to replace NULL with most recent non-null value?(T-SQL:用最新的非空值替换 NULL 的最佳方法?)
Count days in date range with set of exclusions which may overlap(使用一组可能重叠的排除项计算日期范围内的天数)