您好, 欢迎来到 !    登录 | 注册 | | 设为首页 | 收藏本站
问答- 问题:SQL语法：在日期范围之间为每个日期选择结果

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

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

您将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来保持一致性。

SQLServer 2022/1/1 18:44:50 有476人围观

撰写回答

你尚未登录，登录后可以

和开发者交流问题的细节

关注并接收问题和回答的更新提醒

参与内容的编辑和改进，让解决方法与时俱进

请先登录

推荐问题

联系我

联系我

602392714

清零编程清零编程群

置顶