如何在SQL Server查询中比较日期范围并生成年度日维度统计结果？

要实现你想要的按日期分组并对比2017、2018年总金额的查询，我们可以用条件聚合配合日期格式化来完成，下面是具体的实现方案：

基础查询实现 #

假设你的表名为Purchases，以下是核心SQL语句：

SELECT
    -- 格式化日期为"Jan 1"的形式
    CONCAT(DATENAME(MONTH, DateTimePurchased), ' ', DATEPART(DAY, DateTimePurchased)) AS Day,
    -- 统计2017年的总金额
    SUM(CASE WHEN YEAR(DateTimePurchased) = 2017 THEN Amount ELSE 0 END) AS 2017_Total,
    -- 统计2018年的总金额
    SUM(CASE WHEN YEAR(DateTimePurchased) = 2018 THEN Amount ELSE 0 END) AS 2018_Total
FROM Purchases
-- 过滤出2017和2018年的数据（高效写法）
WHERE DateTimePurchased >= '20170101'
  AND DateTimePurchased < '20190101'
-- 按月份和日期分组，确保同月同日的记录被合并
GROUP BY DATEPART(MONTH, DateTimePurchased), DATEPART(DAY, DateTimePurchased), DATENAME(MONTH, DateTimePurchased)
-- 按实际月份顺序排序，避免按字符串字母顺序混乱
ORDER BY DATEPART(MONTH, DateTimePurchased), DATEPART(DAY, DateTimePurchased)

关键细节解释 #

• 日期格式化：用DATENAME(MONTH, ...)获取月份的英文缩写（比如Jan），DATEPART(DAY, ...)获取日期数字，再用CONCAT组合成你需要的Day列格式。
• 条件聚合：SUM(CASE ...)是实现“行转列”统计的常用技巧——只对符合年份条件的Amount求和，不符合的则加0，这样就能在同一行展示两年的总额。
• 高效过滤：用DateTimePurchased >= '20170101' AND DateTimePurchased < '20190101'替代YEAR(DateTimePurchased) IN (2017,2018)，前者可以利用DateTimePurchased字段上的索引，大幅提升查询效率（函数会导致索引失效）。
• 排序与分组：分组时同时包含月份数字、日期数字和月份名称，排序时按月份数字和日期数字排序，确保结果是按自然日期顺序排列（比如Jan→Feb→Mar，而不是按字母顺序的Apr→Aug→Jan）。

可选优化：统一日期格式（比如Jan 01） #

如果你希望日期部分显示为两位数（比如Jan 01而不是Jan 1），可以根据SQL Server版本选择不同的写法：

SQL Server 2012及以上版本

用FORMAT函数简化格式化：

SELECT
    FORMAT(DateTimePurchased, 'MMM dd') AS Day,
    SUM(CASE WHEN YEAR(DateTimePurchased) = 2017 THEN Amount ELSE 0 END) AS 2017_Total,
    SUM(CASE WHEN YEAR(DateTimePurchased) = 2018 THEN Amount ELSE 0 END) AS 2018_Total
FROM Purchases
WHERE DateTimePurchased >= '20170101'
  AND DateTimePurchased < '20190101'
GROUP BY FORMAT(DateTimePurchased, 'MMM dd'), DATEPART(MONTH, DateTimePurchased), DATEPART(DAY, DateTimePurchased)
ORDER BY DATEPART(MONTH, DateTimePurchased), DATEPART(DAY, DateTimePurchased)

兼容更早版本（SQL Server 2008及以下）

用字符串拼接实现两位日期：

SELECT
    CONCAT(DATENAME(MONTH, DateTimePurchased), ' ', RIGHT('0' + CAST(DATEPART(DAY, DateTimePurchased) AS VARCHAR(2)), 2)) AS Day,
    SUM(CASE WHEN YEAR(DateTimePurchased) = 2017 THEN Amount ELSE 0 END) AS 2017_Total,
    SUM(CASE WHEN YEAR(DateTimePurchased) = 2018 THEN Amount ELSE 0 END) AS 2018_Total
FROM Purchases
WHERE DateTimePurchased >= '20170101'
  AND DateTimePurchased < '20190101'
GROUP BY DATEPART(MONTH, DateTimePurchased), DATEPART(DAY, DateTimePurchased), 
         CONCAT(DATENAME(MONTH, DateTimePurchased), ' ', RIGHT('0' + CAST(DATEPART(DAY, DateTimePurchased) AS VARCHAR(2)), 2))
ORDER BY DATEPART(MONTH, DateTimePurchased), DATEPART(DAY, DateTimePurchased)

这样就能完美得到你想要的输出格式啦！

内容的提问来源于stack exchange，提问作者Infin8Loop