如何高效查询每个用户最新日期及对应最新序列的SQL记录

表结构与示例数据 #

假设表名为user_records，结构和数据如下：

username, date, sequence, value
--------------------------
Hannah, 1/2/2010, 0, peach
Kim, 1/3/2010, 0, watermelon
Hannah, 8/4/2009, 0, mango
David, 2/2/2010, 0, apple
David, 2/2/2010, 1, orange
David, 1/3/2010, 0, banana

需求说明 #

需要查询每个用户的最新日期记录；若该日期下存在多条记录，则选取其中sequence值最大的条目。由于表数据量极大，查询必须保证高效，避免CPU过载。

高效查询方案 #

方案一：窗口函数（推荐，多数现代数据库支持） #

使用ROW_NUMBER()窗口函数，按用户分组后先按日期降序、再按sequence降序排序，直接取每组的第一条记录。这种方法只需扫描一次表，执行效率高。

SELECT username, date, sequence, value
FROM (
    SELECT 
        username, 
        date, 
        sequence, 
        value,
        ROW_NUMBER() OVER (
            PARTITION BY username 
            ORDER BY date DESC, sequence DESC
        ) AS rn
    FROM user_records
) AS ranked
WHERE rn = 1;

方案二：关联子查询（兼容旧版数据库） #

通过子查询先定位每个用户的最大日期，以及该日期下的最大sequence，再关联原表获取对应完整记录。

SELECT ur.username, ur.date, ur.sequence, ur.value
FROM user_records ur
INNER JOIN (
    SELECT 
        username, 
        MAX(date) AS max_date,
        MAX(sequence) AS max_seq
    FROM user_records
    GROUP BY username, date
    HAVING date = MAX(date)
) AS sub
ON ur.username = sub.username 
   AND ur.date = sub.max_date 
   AND ur.sequence = sub.max_seq;

性能优化关键 #

针对大数据量场景，必须创建复合索引来避免全表扫描：

CREATE INDEX idx_user_date_seq ON user_records (username, date DESC, sequence DESC);

该索引能让数据库快速定位每个用户的最新日期及对应最大sequence的记录，大幅降低CPU负载。

预期查询结果 #

Hannah, 1/2/2010, 0, peach
Kim, 1/3/2010, 0, watermelon
David, 2/2/2010, 1, orange

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