使用CTE自连接展示有效记录的SQL实现问题

原SQL存在的问题 #

1. 最新记录判定逻辑偏差：原SQL用ID desc排序来标记最新记录，但业务场景中"最新"应基于last_mod_date（记录最后修改时间）而非ID（仅代表插入顺序，无法等同于修改时间的先后）。
2. 有效日期计算逻辑冗余易出错：通过自关联ct1.rn+1 = ct2.rn的方式获取时间边界，逻辑绕且容易因排序规则变动导致关联失效，不如直接用窗口函数简化实现。

修改后的SQL实现 #

WITH EX_CTE AS (
    SELECT 
        ID,
        ex_ID,
        created_date,
        last_mod_date,
        -- 按ex_ID分组，以最后修改时间降序排序，标记最新记录
        ROW_NUMBER() OVER (PARTITION BY ex_ID ORDER BY last_mod_date DESC) AS rn,
        -- 获取同组下一条记录的最后修改时间，用于计算当前记录的有效截止日期
        LEAD(last_mod_date) OVER (PARTITION BY ex_ID ORDER BY last_mod_date) AS next_mod_date
    FROM Exam_T
)
SELECT 
    ID,
    ex_ID,
    created_date,
    last_mod_date,
    CASE WHEN rn = 1 THEN 'Y' ELSE 'N' END AS IS_ACTIVE,
    created_date AS effective_from,
    CASE 
        WHEN rn = 1 THEN '2099-12-31' 
        -- 非活跃记录的有效截止为下一条记录的修改时间（减1分钟避免时间重叠）
        ELSE next_mod_date - INTERVAL '1' MINUTE 
    END AS effective_to
FROM EX_CTE
ORDER BY ex_ID, rn;

核心逻辑说明 #

• 最新记录标记：通过ROW_NUMBER()按ex_ID分组、last_mod_date DESC排序，排名为1的记录即为该组最新记录，标记为IS_ACTIVE='Y'。
• 有效日期范围处理：
  • 活跃记录的有效截止日期固定为2099-12-31。
  • 非活跃记录的有效截止日期取同组下一条记录的last_mod_date并减1分钟，避免两条记录的有效时间出现重叠冲突。
  • 所有记录的有效起始日期均为自身的created_date，符合记录创建即生效的业务逻辑。

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