SQL Server：如何按分组获取每个条目最后2个版本？

嘿，我来帮你搞定这个需求！要拿到每个item的最后两个版本，并整理成item|last_revision|previous_revision的一行式输出，咱们得换个思路——直接用GROUP BY加TOP 2行不通，因为TOP 2是取整个结果集的前2条，而非每个item各自的前2条。这里用窗口函数是最靠谱的方案，下面给你两种可行的实现方式：

方法一：行号标记+条件聚合（兼容性好） #

这个方法适配绝大多数SQL版本（比如SQL Server、MySQL 8+、PostgreSQL等），逻辑清晰易懂：

SELECT 
    item,
    MAX(CASE WHEN rn = 1 THEN revision END) AS last_revision,
    MAX(CASE WHEN rn = 2 THEN revision END) AS previous_revision
FROM (
    -- 内层子查询：给每个item的版本按时间倒序编号，最新的是1，次新的是2
    SELECT 
        item,
        revision,
        ROW_NUMBER() OVER (PARTITION BY item ORDER BY revision DESC) AS rn
    FROM [mytable]
) AS ranked_data
-- 只保留每个item的前两个版本
WHERE rn <= 2
GROUP BY item
-- 按最新版本时间升序排序，和你原来的需求一致
ORDER BY last_revision ASC;

代码解释： #

• ROW_NUMBER() OVER (PARTITION BY item ORDER BY revision DESC)：把数据按item分组，每组内按revision从新到旧排序，给每条记录分配一个序号rn，最新版本的rn=1，次新的rn=2。
• 外层用MAX(CASE...)做条件聚合：把同一个item的rn=1和rn=2的revision分别提取到last_revision和previous_revision列，实现一行展示两个版本。

方法二：LAG窗口函数（简洁版，需高版本支持） #

如果你的数据库支持LAG()函数（比如SQL Server 2022+、PostgreSQL、MySQL 8+），可以用更简洁的写法：

SELECT DISTINCT
    item,
    revision AS last_revision,
    -- 取当前item的上一个（更早的）版本
    LAG(revision) OVER (PARTITION BY item ORDER BY revision DESC) AS previous_revision
FROM [mytable]
-- 只保留每个item的最新版本行，它对应的LAG值就是次新版本
QUALIFY ROW_NUMBER() OVER (PARTITION BY item ORDER BY revision DESC) = 1
ORDER BY last_revision ASC;

代码解释： #

• LAG(revision) OVER (PARTITION BY item ORDER BY revision DESC)：在每个item的分组内，按版本从新到旧排序，取当前行的上一行的revision值，也就是次新版本。
• QUALIFY子句筛选出每个item的最新版本行，此时该行的LAG结果就是对应的次新版本，最后用DISTINCT确保每个item只输出一行。

你之前的写法为什么失败？ #

你尝试的SELECT TOP 2 item, revision as 'last_revision' from [myTable] group by item存在两个核心问题：

1. GROUP BY item会把每个item合并成一行，只能拿到一个版本（默认是任意一个，不是最新的），根本无法获取两个版本。
2. TOP 2是对整个结果集取前2条，而非每个item各自取前2条，逻辑完全不符合需求。

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