如何按月份分组查询各代码的最新记录（含对应count值）

问题分析 #

你原SQL的问题在于将count纳入了GROUP BY分组条件，导致同一code同一月份下，不同count值会被拆分成多个分组，无法准确获取每个code当月最新date对应的count值。

解决方案 #

方法1：使用窗口函数（推荐，适用于SQL Server 2008+、MySQL 8.0+、PostgreSQL等主流数据库） #

通过ROW_NUMBER()窗口函数，按code和月份分组，对每个分组内的记录按date降序排序，标记出每个分组的第一条记录（即当月最新记录）：

WITH monthly_records AS (
    SELECT 
        date,
        -- 生成"YYYY MM"格式的月份字符串
        CONCAT(CAST(YEAR(date) AS VARCHAR(4)), ' ', RIGHT('00' + CAST(MONTH(date) AS VARCHAR(2)), 2)) AS month,
        code,
        count,
        -- 按code和年月分组，按日期倒序编号，最新记录编号为1
        ROW_NUMBER() OVER (PARTITION BY code, YEAR(date), MONTH(date) ORDER BY date DESC) AS rn
    FROM counttable
    WHERE 
        area = 'total'
        AND type = 'cost'
)
SELECT date, month, code, count
FROM monthly_records
WHERE rn = 1
ORDER BY date, code;

方法2：子查询关联（兼容低版本数据库） #

先查询每个code在每个月份的最大日期，再关联原表获取对应count值：

SELECT 
    t.date,
    CONCAT(CAST(YEAR(t.date) AS VARCHAR(4)), ' ', RIGHT('00' + CAST(MONTH(t.date) AS VARCHAR(2)), 2)) AS month,
    t.code,
    t.count
FROM counttable t
INNER JOIN (
    SELECT 
        code,
        YEAR(date) AS record_year,
        MONTH(date) AS record_month,
        MAX(date) AS max_date
    FROM counttable
    WHERE 
        area = 'total'
        AND type = 'cost'
    GROUP BY code, YEAR(date), MONTH(date)
) m ON t.code = m.code 
    AND YEAR(t.date) = m.record_year 
    AND MONTH(t.date) = m.record_month 
    AND t.date = m.max_date
WHERE 
    t.area = 'total'
    AND t.type = 'cost'
ORDER BY t.date, t.code;

说明 #

两种方法都避开了直接对count做聚合，而是先锁定每个(code, 月份)组合的最新日期，再获取该日期对应的count值，完全匹配你要的结果逻辑。其中月份计算简化了原SQL的写法，直接通过YEAR()和MONTH()提取年月生成目标格式。

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