如何高效获取SQL Server表中最新n个唯一ID的所有关联记录？

表结构与示例数据 #

现有SQL Server表包含ID和Date两列，ID字段不唯一，示例数据如下：

ID  Date
1   1/1/2022
2   1/1/2021
3   1/1/2020
1   1/1/1999

需求说明 #

需要提取拥有最新日期的n个唯一ID对应的所有关联记录：

• 当n=1时，返回结果：

ID  Date
1   1/1/2022
1   1/1/1999

• 当n=2时，返回结果：

ID  Date
1   1/1/2022
1   1/1/1999
2   1/1/2021

高效实现方案 #

不需要用自连接，窗口函数或者先筛选目标ID再关联的方式性能更优，以下是两种实用方案：

方案一：单窗口函数直接筛选 #

通过嵌套窗口函数，先计算每个ID的最大日期，再按该日期排名，最后筛选排名前n的ID记录：

DECLARE @n INT = 1; -- 替换为你需要的数值

SELECT ID, Date
FROM (
    SELECT 
        ID,
        Date,
        -- 按每个ID的最大日期倒序排名，同日期ID排名相同
        DENSE_RANK() OVER (ORDER BY MAX(Date) OVER (PARTITION BY ID) DESC) AS rnk
    FROM 你的表名
) AS temp
WHERE rnk <= @n
ORDER BY ID, Date DESC;

方案二：先取目标ID再关联（大数据量首选） #

如果表数据量很大，先单独筛选出前n个符合条件的ID，再关联原表获取全量记录，能大幅减少计算量：

DECLARE @n INT = 2;

WITH TopTargetIDs AS (
    SELECT ID
    FROM (
        SELECT 
            ID,
            DENSE_RANK() OVER (ORDER BY MAX(Date) DESC) AS rnk
        FROM 你的表名
        GROUP BY ID
    ) AS rankedIDs
    WHERE rnk <= @n
)
SELECT t.ID, t.Date
FROM 你的表名 t
JOIN TopTargetIDs ti ON t.ID = ti.ID
ORDER BY t.ID, Date DESC;

注意事项 #

• 用DENSE_RANK()而不是ROW_NUMBER()的原因：如果多个ID的最新日期相同，DENSE_RANK()会给它们相同的排名，不会遗漏符合条件的ID；如果用ROW_NUMBER()会强制给同日期ID分配不同排名，可能导致少选ID，根据实际需求调整。
• 若给表的ID和Date字段建立联合索引，两种方案的性能会进一步提升，尤其是方案二的分组排名阶段可以直接利用索引完成计算。

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