如何调整SQL查询以获取指定更新记录及其历史行

表结构与示例数据 #

我有一张用于追踪INSERT、UPDATE、DELETE操作的历史表ANC_PER_ABS_ENTRIES，表结构及示例数据如下：

需求说明 #

需编写SQL查询，基于参数:process_date筛选数据：当同一PER_AB_ENTRY_ID的最新记录LAST_UPD_DT >= :process_date时，需同时提取该记录及其前一行历史数据（如示例中PER_AB_ENTRY_ID=15的INSERT行和UPDATE行）。

原查询问题 #

当前查询因历史行LAST_UPD_DT <= :process_date无法同时取出两行，仅能处理DELETE场景，原查询代码如下：

with anc as 
(
select person_number,
absence_type,
ABSENCE_STATUS,
approval_status_cd,
start_date,
end_date,
duration,
PER_AB_ENTRY_ID,
AUDIT_ACTION_TYPE_,
         row_number() over (order by PER_AB_ENTRY_ID, LAST_UPD_DT) rn
from ANC_PER_ABS_ENTRIES
)
SELECT * FROM ANC 
where    RN = 1
   or    RN = 2 and UPPER(flag) = 'D'
   and APPROVAL_STATUS_CD = 'Approved'
   and last_update_date >=:process_date
ORder by PER_AB_ENTRY_ID, LAST_UPD_DT

调整后的查询 #

写法一：基于倒序行号筛选 #

with anc as (
    select 
        person_number,
        absence_type,
        ABSENCE_STATUS,
        approval_status_cd,
        start_date,
        end_date,
        duration,
        PER_AB_ENTRY_ID,
        AUDIT_ACTION_TYPE_,
        LAST_UPD_DT,
        -- 按ID分区，按更新时间倒序分配行号，最新记录行号为1
        row_number() over (partition by PER_AB_ENTRY_ID order by LAST_UPD_DT desc) as rn_desc,
        -- 判断当前ID的最新记录是否满足时间条件
        max(LAST_UPD_DT) over (partition by PER_AB_ENTRY_ID) >= :process_date as is_eligible
    from ANC_PER_ABS_ENTRIES
    where APPROVAL_STATUS_CD = 'Approved'
)
SELECT 
    person_number,
    absence_type,
    ABSENCE_STATUS,
    approval_status_cd,
    start_date,
    end_date,
    duration,
    PER_AB_ENTRY_ID,
    AUDIT_ACTION_TYPE_
FROM anc
WHERE is_eligible = 1
  AND rn_desc <= 2
ORDER BY PER_AB_ENTRY_ID, LAST_UPD_DT;

写法二：基于正序行号计算目标范围 #

with anc as (
    select 
        person_number,
        absence_type,
        ABSENCE_STATUS,
        approval_status_cd,
        start_date,
        end_date,
        duration,
        PER_AB_ENTRY_ID,
        AUDIT_ACTION_TYPE_,
        LAST_UPD_DT,
        -- 按ID分区，按更新时间正序分配行号
        row_number() over (partition by PER_AB_ENTRY_ID order by LAST_UPD_DT) as rn,
        -- 获取当前ID的最大行号（即记录总数）
        count(*) over (partition by PER_AB_ENTRY_ID) as total_rows,
        -- 判断当前ID的最新记录是否满足时间条件
        max(LAST_UPD_DT) over (partition by PER_AB_ENTRY_ID) >= :process_date as is_eligible
    from ANC_PER_ABS_ENTRIES
    where APPROVAL_STATUS_CD = 'Approved'
)
SELECT 
    person_number,
    absence_type,
    ABSENCE_STATUS,
    approval_status_cd,
    start_date,
    end_date,
    duration,
    PER_AB_ENTRY_ID,
    AUDIT_ACTION_TYPE_
FROM anc
WHERE is_eligible = 1
  AND rn >= total_rows - 1
ORDER BY PER_AB_ENTRY_ID, LAST_UPD_DT;

调整说明 #

1. 窗口函数分区：原查询未按PER_AB_ENTRY_ID分区，导致row_number()为全局排序，无法识别同一ID下的历史行。调整后通过partition by PER_AB_ENTRY_ID对每个ID单独计算行号。
2. ** eligibility判断**：新增窗口函数max(LAST_UPD_DT) over (partition by PER_AB_ENTRY_ID)获取每个ID的最新更新时间，判断是否满足>= :process_date的条件，避免了原查询依赖历史行时间的问题。
3. 目标行筛选：通过倒序行号（rn_desc <=2）或正序行号范围（rn >= total_rows -1），直接取符合条件ID的最新两行，确保即使历史行时间早于参数也能被取出。
4. 逻辑简化：将APPROVAL_STATUS_CD = 'Approved'过滤提前到CTE中减少计算量；移除原查询中仅针对DELETE场景的UPPER(flag) = 'D'条件，适配所有操作类型。

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