SQL筛选非重复行时遗漏首条含assign值的行如何解决

• 现有SQL的WHERE条件(status_old != status_new OR status_old IS NULL)将status_old与status_new相等的行全部过滤，你需要保留的缺失行恰好是D→D的状态，符合被过滤的条件，所以没有返回。
• 原有条件(assign IS NULL OR assign IS NOT NULL)无实际作用，所有行都满足该判断，可以直接删除。
• 拼写错误：SELECT子句中的assig应为assign。
• 逻辑顺序错误：窗口函数LEAD的执行顺序晚于WHERE子句，先过滤再计算偏移值会导致结果不符合预期，同时原有GROUP BY逻辑冗余，且引用了未计算的pre_status字段，存在逻辑错误。

INSERT INTO #sub_tableY (ID, status_old, status_new, assign, pre_status, S_TIMESTAMP, S_TIMESTAMP_next)
WITH marked_table AS (
    SELECT  
        ID, status_old, status_new, assign,
        -- 标记当前ID下第一个assign不为空的行
        CASE WHEN assign IS NOT NULL AND LAG(assign,1,NULL) OVER (PARTITION BY ID ORDER BY S_TIMESTAMP) IS NULL 
             THEN 1 ELSE 0 END AS is_first_assign,
        -- 提前计算LEAD值，避免过滤后偏移错误
        LEAD(status_new, 1, NULL) OVER (PARTITION BY ID ORDER BY S_TIMESTAMP) pre_status, 
        S_TIMESTAMP,
        LEAD(S_TIMESTAMP, 1, NULL) OVER (PARTITION BY ID ORDER BY S_TIMESTAMP) S_TIMESTAMP_next
    FROM [TABLE]
)
SELECT ID, status_old, status_new, assign, pre_status, S_TIMESTAMP, S_TIMESTAMP_next
FROM marked_table
WHERE 
    -- 原有保留条件
    (status_old != status_new OR status_old IS NULL)
    -- 新增：保留首次assign不为空的行
    OR is_first_assign = 1
-- 如需额外去重可在SELECT后添加DISTINCT

• 新增CTE先给所有原始行打标记，用LAG函数判断当前行是否是当前ID下第一个assign不为空的行，确保需要保留的特殊行被标记。
• 调整WHERE条件，在原有保留规则基础上新增对首次assign非空行的保留逻辑。
• 先计算LEAD偏移值再过滤，保证pre_status和S_TIMESTAMP_next的取值和原始表顺序一致。
• 移除冗余判断和错误的GROUP BY逻辑。

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