SQL查询：如何获取每个CustomerID最新日期的最后一条Counter记录？

需求说明 #

从存储用户每日数据的表中（部分用户单日存在多条记录），提取每个CustomerID对应最新日期下的最后一条记录。

示例数据 #

预期结果 #

解决方案 #

方法1：窗口函数（推荐，兼容多数现代数据库） #

使用ROW_NUMBER()窗口函数按用户分组排序，直接筛选目标记录，这是最简洁且不易出错的方式。

WITH ranked_records AS (
    SELECT 
        Day,
        CustomerID,
        Counter,
        -- 按用户分组，先按日期倒序，再按Counter倒序（有主键则用主键倒序更准确）
        ROW_NUMBER() OVER (
            PARTITION BY CustomerID 
            ORDER BY Day DESC, Counter DESC
        ) AS record_rank
    FROM your_table_name
)
SELECT Day, CustomerID, Counter
FROM ranked_records
WHERE record_rank = 1;

代码说明：

• PARTITION BY CustomerID：将数据按用户ID拆分，每个用户单独处理
• ORDER BY Day DESC：确保每个用户的最新日期排在最前面
• ORDER BY ... Counter DESC：处理单日多条记录的情况，取Counter最大的那条（如果表有唯一主键，比如RecordID，替换为RecordID DESC能彻底避免重复值导致的歧义）
• record_rank = 1：筛选出每个用户分组中排名第一的记录，即目标结果

方法2：关联子查询（兼容MySQL 5.x等不支持窗口函数的数据库） #

如果数据库不支持窗口函数，可通过子查询先获取每个用户的最新日期，再筛选该日期下的最后一条记录。

SELECT t1.Day, t1.CustomerID, t1.Counter
FROM your_table_name t1
-- 关联子查询获取每个用户的最新日期
INNER JOIN (
    SELECT 
        CustomerID,
        MAX(Day) AS latest_day
    FROM your_table_name
    GROUP BY CustomerID
) t2 ON t1.CustomerID = t2.CustomerID AND t1.Day = t2.latest_day
-- 筛选该日期下的最后一条记录（用主键替换Counter更可靠）
WHERE t1.Counter = (
    SELECT MAX(Counter)
    FROM your_table_name
    WHERE CustomerID = t1.CustomerID AND Day = t1.Day
);

注意事项 #

• 替换代码中的your_table_name为实际数据表名
• 如果表存在唯一主键（如自增ID），排序时优先使用主键倒序，能确保唯一确定最后一条记录，避免字段值重复的问题
• 示例数据中20240104的CustomerID=20240104为输入笔误，上述代码会正常处理该用户的最新记录

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