如何在MySQL中按日期范围获取每个客户的最新记录

问题描述 #

需要在MySQL中查询日期范围'2022-08-01'至'2022-08-03'内每个客户的最新记录，现有表结构及数据如下：

原始表数据 #

预期输出 #

解决方案 #

方法1：使用窗口函数（MySQL 8.0及以上版本） #

利用ROW_NUMBER()窗口函数按客户分组，在日期范围内按date降序排序，筛选每组第一条记录：

SELECT id, cust_name, amount, date
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY cust_name ORDER BY date DESC) AS rn
    FROM your_table_name
    WHERE date BETWEEN '2022-08-01' AND '2022-08-03'
) t
WHERE rn = 1;

• PARTITION BY cust_name：按客户维度分组
• ORDER BY date DESC：每组内按日期倒序排列，最新记录排在首位
• rn = 1：提取每组的第一条（最新）记录

方法2：适用于MySQL 5.x版本（无窗口函数支持） #

先通过子查询获取每个客户在指定日期内的最大日期，再关联原表匹配完整记录：

SELECT t1.id, t1.cust_name, t1.amount, t1.date
FROM your_table_name t1
INNER JOIN (
    SELECT cust_name, MAX(date) AS max_date
    FROM your_table_name
    WHERE date BETWEEN '2022-08-01' AND '2022-08-03'
    GROUP BY cust_name
) t2 ON t1.cust_name = t2.cust_name AND t1.date = t2.max_date
WHERE t1.date BETWEEN '2022-08-01' AND '2022-08-03';

1. 子查询t2统计每个客户在目标日期范围内的最新日期
2. 关联原表t1，匹配客户名称与对应最新日期，获取完整记录
3. 二次过滤日期范围，避免意外匹配到范围外的数据

若存在同一客户同一日期有多条记录的情况，可调整为取最大id的记录：

SELECT t1.id, t1.cust_name, t1.amount, t1.date
FROM your_table_name t1
INNER JOIN (
    SELECT cust_name, MAX(date) AS max_date, MAX(id) AS max_id
    FROM your_table_name
    WHERE date BETWEEN '2022-08-01' AND '2022-08-03'
    GROUP BY cust_name
) t2 ON t1.cust_name = t2.cust_name AND t1.id = t2.max_id
WHERE t1.date BETWEEN '2022-08-01' AND '2022-08-03';

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