SQL Server查询以获取一年中每个月的总计，并以日期格式而不是字符串格式返回年份和月份

2024-05-20 • 问答

我遇到一种情况，我必须计算一年中每个月的“总计”，并且必须在保持datetime格式的同时显示一年中每个月的总计。我正在编写以下查询： / p>

SELECT [DATE_FORMATION],sum(CO) CO,sum(Total_Members) Total_Members
from (
    select  convert(varchar(7),CAST([DATE_FORMATION] AS DATE),126) as [DATE_FORMATION],count([CO_ID]) as CO,sum(convert(int,[TOTAL_MEMberS])) as Total_Members
    from COCores
    where cast([DATE_FORMATION] as date) >= Dateadd(Month,Datediff(Month,DATEADD(m,-10,current_timestamp)),0)
    group by [DATE_FORMATION]
) tmp
group by [DATE_FORMATION]

它给出了我所需的准确答案

但是问题是它以“ Varchar”格式生成日期，而我需要以datetime格式生成日期，以便我可以进一步使用它。如何在将日期保持为datetime格式并获得相同结果的同时获得此结果。

您可以像这样将所有日期转换为一个月的第一天：

DATEADD(DAY,1,EOMONTH(DATE_FORMATION,-1))

它产生一个DATE。您可以在GROUP BY和SELECT子句中使用此表达式：

SELECT DATEADD(DAY,-1)),COUNT(CO_ID) AS CO,SUM(CONVERT(INT,TOTAL_MEMBERS)) AS Total_Members
FROM COCores
WHERE DATE_FORMATION >= -- calculate -10 months
GROUP BY DATEADD(DAY,-1))

日期没有格式，它们是二进制值，就像int或decimal或binary一样。 DATE_FORMATION应该是日期类型，例如date，datetime或datetime2。如果该列使用正确的类型，则查询可以简化为：

select
    datefromparts(Year(DATE_FORMATION),MONTH(DATE_FORMATION),1) as DATE_FORMATION,count([CO_ID]) as CO,sum([TOTAL_MEMBERS]) as Total_Members
from COCores
where 
    [DATE_FORMATION] as date) >= DATEFROMPARTS(Year(current_timestamp),1)
group by 
    YEAR(DATE_FORMATION),MONTH(DATE_FORMATION)

DATEFROMPARTS(Year(current_timestamp),1)返回当年的第一个日期。用于仅返回当年的行。使用YEAR和MONTH函数将结果按DATE_FORMATION的年和月分组。最后，SELECT通过使用DATEFROMPARTS从组键生成日期值来返回每个月的第一天

如果您使用a Calendar table，

这样的查询将变得更容易。日历表包含每个日期的行，例如50年，以及年，月，日，周，名称等的额外列，这些列可用于简化报告。日历表具有大量索引，可轻松按年，季度，学期等进行查询。

假设DATE_FORMATION是date，并且日历表具有日期，年，月和 StartOfMonth 列，则可以将查询转换为：

select
    Calendar.StartOfMonth,sum([TOTAL_MEMBERS]) as Total_Members
from COCores inner join Calendar on DATE_FORMATION=Calendar.date
where 
    Calendar.Year= Year(current_timestamp)
group by 
    StartOfMonth

此查询也将很快，因为它可以利用Year，Date和StartOfMonth上的任何索引

SQL Server查询以获取一年中每个月的总计，并以日期格式而不是字符串格式返回年份和月份

jmj1018856792 回答：SQL Server查询以获取一年中每个月的总计，并以日期格式而不是字符串格式返回年份和月份

大家都在问