如何从物品审计历史表中提取指定对象的最新修改/操作记录

你之前的写法仅做了排序没有做过滤，所以会返回全部记录，以下是三种不同场景下的可靠实现方案：

方案1：窗口函数法（推荐，适配MySQL 8.0+/PostgreSQL/Oracle等主流新版数据库） #

支持单物品查询，也可以无缝扩展为批量查询全量物品的最新记录：

SELECT * 
FROM (
    SELECT 
        *,
        -- 按物品ID分组，同组内按修改时间倒序编号
        ROW_NUMBER() OVER (PARTITION BY 物品ID ORDER BY 修改时间 DESC) AS rn
    FROM 物品审计表
    -- 仅查询单个指定物品时加该行，查询所有物品最新记录直接删除即可
    WHERE 物品ID = '目标物品的ID'
) temp
WHERE rn = 1;

• 如果存在同一时间点有多条该物品的修改记录，且需要全部返回时，把ROW_NUMBER()替换为RANK()即可。

方案2：排序取顶法（单物品查询最简写法） #

仅需要查询单个物品的最新记录时，写法最简单：

SELECT * 
FROM 物品审计表
WHERE 物品ID = '目标物品的ID'
ORDER BY 修改时间 DESC
LIMIT 1;

• SQL Server环境下将LIMIT 1替换为TOP 1写在SELECT后即可；Oracle环境下在末尾加AND ROWNUM = 1即可。

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

SELECT a.*
FROM 物品审计表 a
INNER JOIN (
    SELECT 物品ID, MAX(修改时间) AS 最新修改时间
    FROM 物品审计表
    WHERE 物品ID = '目标物品的ID'
    GROUP BY 物品ID
) b ON a.物品ID = b.物品ID AND a.修改时间 = b.最新修改时间
WHERE a.物品ID = '目标物品的ID';

内容的提问来源于stack exchange，提问作者Khiêm Cao