如何基于参考日期获取每个ID对应的最近有效行？

需要构建一份报表，输入指定参考日期后，返回每个ID（人员）对应的最接近该日期且不晚于该日期的有效行，要求每个ID仅返回一行。

原始表结构及数据 #

示例1：参考日期为01/02/2023时的期望结果 #

示例2：参考日期为01/01/2021时的期望结果 #

尝试的SQL（仅返回单个ID的行，不符合需求） #

DECLARE    @ReportingDate date
SET    @ReportingDate = '01-01-2023'

SELECT     TOP 1
       ID, Name, Value, [Effective From]
FROM       Table
WHERE      [Effective From] <= @ReportingDate
ORDER BY   [Effective From] DESC

解决方案：使用窗口函数ROW_NUMBER() #

通过ROW_NUMBER()窗口函数按ID分组排序，取每个ID下最接近参考日期的行：

DECLARE @ReportingDate date
SET @ReportingDate = '01-01-2023'

WITH RankedRows AS (
    SELECT 
        ID, Name, Value, [Effective From],
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [Effective From] DESC) AS RowRank
    FROM 
        Table
    WHERE 
        [Effective From] <= @ReportingDate
)
SELECT 
    ID, Name, Value, [Effective From]
FROM 
    RankedRows
WHERE 
    RowRank = 1;

说明： #

• PARTITION BY ID：按ID分组，确保每个ID单独处理
• ORDER BY [Effective From] DESC：在每个ID组内，按生效日期从晚到早排序，最接近参考日期的行排第1
• 筛选RowRank = 1即可得到每个ID的目标行

如果存在多个行的Effective From完全等于参考日期，需返回所有符合行的话，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()。

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