如何基于AffectedDate字段获取各年份每月最后一天的记录

嘿，我完全懂你的需求——你不需要那些基于GetDate()生成当前月末的方法，而是要从已有的AffectedDate字段里，精准找出每个年份、每个月最后一天对应的所有记录，对吧？这是个很常见的业务场景，我给你几个不同数据库环境下的实用解决方案，你可以按需选用：

通用子查询关联法（适配大多数数据库） #

这个方法逻辑很直观：先找出每个年月的最后一天（也就是该年月的最大日期），再关联原表获取对应记录。不管你用SQL Server、MySQL还是PostgreSQL都能直接用：

SELECT t.*
FROM YourTableName t
INNER JOIN (
    -- 先找出每个年月的最后一天
    SELECT 
        YEAR(AffectedDate) AS RecordYear,
        MONTH(AffectedDate) AS RecordMonth,
        MAX(AffectedDate) AS LastDayOfMonth
    FROM YourTableName
    GROUP BY YEAR(AffectedDate), MONTH(AffectedDate)
) m ON 
    YEAR(t.AffectedDate) = m.RecordYear 
    AND MONTH(t.AffectedDate) = m.RecordMonth 
    AND t.AffectedDate = m.LastDayOfMonth;

如果你的AffectedDate包含时间部分（比如2024-05-31 16:45:00），这个方法依然有效，因为MAX(AffectedDate)会自动取当天的最晚时间记录。要是你只想按日期部分匹配，可以把AffectedDate转成纯日期格式，比如SQL Server用CAST(AffectedDate AS DATE)，MySQL用DATE(AffectedDate)。

窗口函数法（更简洁，适合支持窗口函数的数据库） #

如果你的数据库支持窗口函数（现在主流数据库基本都支持），用这个方法会更简洁，还能灵活处理“当月最后一天有多条记录”的情况：

SQL Server / MySQL 版本 #

WITH MonthlyRecords AS (
    SELECT 
        *,
        -- 用RANK()而不是ROW_NUMBER()，这样同一天的多条记录都会被保留
        RANK() OVER (
            PARTITION BY YEAR(AffectedDate), MONTH(AffectedDate) 
            ORDER BY AffectedDate DESC
        ) AS RecordRank
    FROM YourTableName
)
SELECT *
FROM MonthlyRecords
WHERE RecordRank = 1;

• 划重点：如果用ROW_NUMBER()，哪怕是同一天的记录也会被编上不同的序号，只能取到一条；换成RANK()或DENSE_RANK()就能保留当月最后一天的所有记录。

PostgreSQL 版本 #

PostgreSQL可以用DATE_TRUNC函数更简洁地按月份分组：

WITH MonthlyRecords AS (
    SELECT 
        *,
        RANK() OVER (
            PARTITION BY DATE_TRUNC('month', AffectedDate) 
            ORDER BY AffectedDate DESC
        ) AS RecordRank
    FROM YourTableName
)
SELECT *
FROM MonthlyRecords
WHERE RecordRank = 1;

优化小提示 #

• 如果你的表数据量很大，一定要给AffectedDate字段创建索引，这样分组、排序的效率会提升很多；
• 要是存在历史数据缺失（比如某个月没有任何记录），上述方法会自动跳过该年月，不会生成空行，符合常规业务需求。

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