使用ROW_NUMBER() OVER筛选不晚于截止日期的最近匹配日期并保留全量数据

原SQL使用ABS(DATEDIFF(...))排序的核心问题是：它会把早于Game_Date N天和晚于Game_Date N天的记录视为同等优先级，完全忽略了"优先保留不晚于截止日的最近记录，再处理晚于截止日记录"的核心需求。

解决方案 #

通过拆分排序维度，分两步实现需求，同时保留全量数据用于审计：

1. 先按"是否不晚于Game_Date"划分优先级组，确保合规记录（Entry_Date ≤ Game_Date）排在前面
2. 在每个组内，按与Game_Date的距离由近到远排序
3. 用ROW_NUMBER()生成行号

调整后的SQL代码 #

假设你需要按业务维度（比如玩家ID Player_ID）分组排序，若无分组需求可移除PARTITION BY子句：

SELECT 
    *,
    ROW_NUMBER() OVER (
        PARTITION BY Player_ID  -- 根据实际业务逻辑调整分组键
        ORDER BY 
            -- 第一优先级：不晚于Game_Date的记录排前面
            CASE WHEN Entry_Date <= Game_Date THEN 0 ELSE 1 END,
            -- 第二优先级：组内按距离Game_Date由近到远排序
            CASE 
                WHEN Entry_Date <= Game_Date THEN DATEDIFF(DAY, Entry_Date, Game_Date)  -- 早于/等于的组，距离越小越靠前（即Entry_Date越大越近）
                ELSE DATEDIFF(DAY, Game_Date, Entry_Date)  -- 晚于的组，距离越小越靠前（即Entry_Date越小越近）
            END
    ) AS Row_Num
FROM Vitals;

或者更直观的写法，直接用Entry_Date排序：

SELECT 
    *,
    ROW_NUMBER() OVER (
        PARTITION BY Player_ID
        ORDER BY 
            CASE WHEN Entry_Date <= Game_Date THEN 0 ELSE 1 END,
            -- 不晚于组：最近的记录（最大的Entry_Date）排第一
            CASE WHEN Entry_Date <= Game_Date THEN Entry_Date END DESC,
            -- 晚于组：最接近的记录（最小的Entry_Date）排第一
            CASE WHEN Entry_Date > Game_Date THEN Entry_Date END ASC
    ) AS Row_Num
FROM Vitals;

逻辑解释 #

• 第一个排序条件CASE WHEN Entry_Date <= Game_Date THEN 0 ELSE 1 END：将合规记录标记为0，违规记录标记为1，确保合规组整体排在前面
• 第二个排序条件：
  • 对于合规组，通过降序排列Entry_Date，让最接近Game_Date的记录（即最大的Entry_Date）获得最小的行号
  • 对于违规组，通过升序排列Entry_Date，让最接近Game_Date的记录（即最小的Entry_Date）在本组内排在最前面

验证示例 #

假设某玩家的Game_Date是2024-05-10，Entry_Date分别为2024-05-08、2024-05-11、2024-05-09：

• 排序后行号依次为：2024-05-09（1）、2024-05-08（2）、2024-05-11（3），完全符合需求

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