如何避免获取多ID最新记录时的SQL JOIN操作？

你可以用窗口函数替代JOIN来实现需求，无需关联原表即可获取每个ID的最新记录。以下是具体实现方案：

当前使用的查询语句 #

SELECT t.*
FROM   (SELECT id,
               max(date) AS latest_date
        FROM   table_name
        WHERE  id IN ( 1, 2, 3, 4 )
        GROUP  BY id) AS latest_records
       JOIN table_name t
         ON t.id = latest_records.id
            AND t.date = latest_records.latest_date 

示例表结构及数据 #

id, date, column
1, 24-2-2, data
1, 24-2-1, data1
2, 24-2-2, data
2, 24-2-1, data1

期望返回结果 #

id, date, column
1, 24-2-2, data
2, 24-2-2, data

无JOIN的实现方案 #

方法1：使用ROW_NUMBER()（单条最新记录） #

如果每个ID只需要保留一条最新记录（即使存在多条同日期的最新记录，随机取其一），可以用ROW_NUMBER()窗口函数：

SELECT id, date, column
FROM (
    SELECT 
        id, 
        date, 
        column,
        -- 按ID分组，每组内按日期倒序排序，标记行号
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS row_num
    FROM table_name
    WHERE id IN (1, 2, 3, 4)
) AS ranked_records
WHERE row_num = 1;

方法2：使用RANK()（保留所有同日期的最新记录） #

如果同一个ID存在多条日期相同的最新记录，需要全部保留，可以用RANK()替代ROW_NUMBER()：

SELECT id, date, column
FROM (
    SELECT 
        id, 
        date, 
        column,
        -- 同日期的记录会获得相同的排名
        RANK() OVER (PARTITION BY id ORDER BY date DESC) AS rank_num
    FROM table_name
    WHERE id IN (1, 2, 3, 4)
) AS ranked_records
WHERE rank_num = 1;

说明 #

• 窗口函数是SQL标准语法，支持MySQL 8.0+、PostgreSQL、SQL Server、Oracle等主流数据库。
• 若表上存在(id, date)的复合索引，该查询的性能会更优，数据库可以直接按索引顺序读取数据，避免额外排序。

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