如何在SQL中筛选数据集中每个客户的最新记录

原始数据 #

期望结果 #

原SQL问题分析 #

原SQL语句：

Select distinct
merchant_name,
merchant_id,
revenue_date,
revenue_amount
from table
where revenue_date=(select max(revenue_date) from table)

问题出在子查询select max(revenue_date) from table取的是全局最大日期，而非每个商户自己的最新日期，因此只能返回匹配全局最新日期的cat记录，无法拿到每个商户的最新数据。

解决方法 #

方法1：关联子查询（兼容所有SQL方言） #

先按商户分组计算每个商户的最新日期，再和原表关联筛选对应记录：

SELECT t.merchant_name,
       t.merchant_id,
       t.revenue_date,
       t.revenue_amount
FROM your_table t
INNER JOIN (
    SELECT merchant_id, MAX(revenue_date) AS max_date
    FROM your_table
    GROUP BY merchant_id
) sub ON t.merchant_id = sub.merchant_id AND t.revenue_date = sub.max_date

逻辑说明：子查询得到每个商户的ID和对应的最新日期，再通过商户ID和日期匹配原表，筛选出每个商户最新日期的行。

方法2：窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等） #

利用ROW_NUMBER()窗口函数对每个商户的记录按日期倒序编号，取每组第一行：

SELECT merchant_name, merchant_id, revenue_date, revenue_amount
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY merchant_id ORDER BY revenue_date DESC) AS rn
    FROM your_table
) t
WHERE rn = 1

逻辑说明：

• PARTITION BY merchant_id：按商户ID分组
• ORDER BY revenue_date DESC：每组内按日期从新到旧排序
• ROW_NUMBER()：给每组的行分配序号，最新日期的行序号为1
• 最后筛选rn=1即可得到每个商户的最新记录

如果同一商户在最新日期有多条记录，想保留所有这些记录，可以把ROW_NUMBER()换成RANK()或DENSE_RANK()。

方法3：GROUP BY聚合（仅适用于同一商户最新日期唯一的场景） #

如果每个商户在最新日期只有一条记录，可以直接分组聚合：

SELECT merchant_id,
       MAX(merchant_name) AS merchant_name,
       MAX(revenue_date) AS revenue_date,
       MAX(revenue_amount) AS revenue_amount
FROM your_table
GROUP BY merchant_id

注意：此方法仅当同一商户最新日期下只有一条记录时可用，否则revenue_amount会取最大值，不符合需求。

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