带回溯与未来日期场景的有效日期筛选技术问询

我来帮你搞定这个需求！根据你给出的两个场景，核心要处理两种覆盖逻辑：同STARTDT的记录只保留最新RUN_ID的那条，以及晚更新的更早STARTDT记录会覆盖所有比它晚的STARTDT旧记录。下面是可以直接运行的SQL解决方案，我会一步步拆解逻辑：

实现SQL #

-- 先处理场景1的测试数据（场景2只需替换#RUN_ID的插入数据即可）
IF OBJECT_ID('TEMPDB..#RUN_ID') IS NOT NULL DROP TABLE #RUN_ID;
WITH RUN_ID AS (
    SELECT 1 AS RUN_ID,1 AS EMP_ID, '1/1/2018' STARTDT, 'A' AS VALUE UNION
    SELECT 2 AS RUN_ID,1 AS EMP_ID, '2/1/2018' STARTDT, 'A' AS VALUE UNION
    SELECT 3 AS RUN_ID,1 AS EMP_ID, '12/1/2017' STARTDT, 'A' AS VALUE UNION
    SELECT 4 AS RUN_ID,1 AS EMP_ID, '3/1/2018' STARTDT, 'A' AS VALUE UNION
    SELECT 5 AS RUN_ID,1 AS EMP_ID, '2/1/2018' STARTDT, 'A' AS VALUE
)
SELECT * INTO #RUN_ID FROM RUN_ID;

-- 核心逻辑
WITH LatestPerStartDt AS (
    SELECT 
        RUN_ID,
        EMP_ID,
        STARTDT,
        VALUE,
        -- 给每个EMP_ID+STARTDT分组，标记最新RUN_ID的记录
        ROW_NUMBER() OVER (PARTITION BY EMP_ID, STARTDT ORDER BY RUN_ID DESC) AS rn
    FROM #RUN_ID
),
FilteredLatest AS (
    -- 第一步：保留每个日期的最新记录（解决同日期覆盖）
    SELECT RUN_ID, EMP_ID, STARTDT, VALUE
    FROM LatestPerStartDt
    WHERE rn = 1
),
ValidRecords AS (
    SELECT 
        RUN_ID,
        EMP_ID,
        STARTDT,
        VALUE,
        -- 按RUN_ID降序（最新更新在前），计算到当前记录为止的最小STARTDT
        MIN(STARTDT) OVER (PARTITION BY EMP_ID, VALUE ORDER BY RUN_ID DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MinStartDtSoFar
    FROM FilteredLatest
)
-- 第二步：只保留STARTDT等于当前最小日期的记录（解决早日期覆盖晚日期）
SELECT RUN_ID, EMP_ID, STARTDT, VALUE
FROM ValidRecords
WHERE STARTDT = MinStartDtSoFar
ORDER BY RUN_ID;

逻辑拆解 #

1. LatestPerStartDt：通过ROW_NUMBER()给每个EMP_ID+STARTDT的记录按RUN_ID降序编号，编号为1的就是该日期的最新记录（因为RUN_ID越大更新越晚）。
2. FilteredLatest：过滤出每个日期的最新记录，先解决同STARTDT的覆盖问题。
3. ValidRecords：对每个EMP_ID+VALUE，按RUN_ID降序排列（最新的更新排在最前面），然后计算从第一条到当前记录的最小STARTDT。如果当前记录的STARTDT等于这个最小值，说明这条记录的日期是到目前为止最早的，不会被后续（其实是更晚的更新）的记录覆盖；反之，如果日期比最小值大，说明已经有更早的日期在更晚的更新中出现，这条记录就失效了。
4. 最后筛选出符合条件的记录，就是我们需要的有效数据。

场景验证 #

场景1验证 #

处理后得到的有效记录是：

RUN_ID EMP_ID STARTDT   VALUE
3      1      12/1/2017 A
5      1      2/1/2018  A

完全符合你的预期：RUN_ID5覆盖了同STARTDT的RUN_ID2，RUN_ID3作为更早日期的晚更新，覆盖了所有比它晚的STARTDT记录（1/1/2018、3/1/2018）。

场景2验证 #

把#RUN_ID的测试数据替换为场景2的内容：

WITH RUN_ID AS (
    SELECT 1 AS RUN_ID,1 AS EMP_ID, '1/1/2018' STARTDT, 'A' AS VALUE UNION
    SELECT 2 AS RUN_ID,1 AS EMP_ID, '11/1/2017' STARTDT, 'A' AS VALUE UNION
    SELECT 3 AS RUN_ID,1 AS EMP_ID, '12/1/2017' STARTDT, 'A' AS VALUE UNION
    SELECT 4 AS RUN_ID,1 AS EMP_ID, '3/1/2018' STARTDT, 'A' AS VALUE UNION
    SELECT 5 AS RUN_ID,1 AS EMP_ID, '2/1/2018' STARTDT, 'A' AS VALUE
)
SELECT * INTO #RUN_ID FROM RUN_ID;

运行核心逻辑后得到的结果是：

RUN_ID EMP_ID STARTDT   VALUE
2      1      11/1/2017 A
3      1      12/1/2017 A
5      1      2/1/2018  A

也完全符合预期：不同的回溯日期（11/1/2017、12/1/2017）都保留，RUN_ID5覆盖了同STARTDT的旧记录，同时覆盖了更晚的STARTDT（3/1/2018）的记录。

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