Oracle 19：基于第三列最大值筛选子集并获取对应ID

现有一张包含4列及以上的summary表，结构为(ID1, ID2, SortOrder, BooleanYN1, BooleanYN2)。需求是：针对每个ID1值，获取满足BooleanYN{n} = 'Y'且SortOrder值最大的行对应的ID2，若无符合条件的记录则返回NULL。

示例数据 #

示例期望结果：(1, 30, NULL)，因为ID1 = 1时，BooleanYN1 = 'Y'且SortOrder最大的行对应ID2 = 30，而无BooleanYN2 = 'Y'的行。

原查询问题 #

以下查询在Oracle 19中可行，但Oracle 11g不支持OUTER APPLY，且写法繁琐低效：

SELECT
  p.person_id,
  mmr.appointment_id,
  flu.appointment_id,
  covid.appointment_id,
  hiv.appointment_id
FROM
  person p
  OUTER APPLY (
    SELECT vs.appointment_id
      FROM vaccination_summary vs
     WHERE vs.person_id = p.person_id
       AND mmr_yn = 'Y'
     ORDER BY vs.appointment_date DESC
     FETCH FIRST ROW ONLY
  ) mmr
  OUTER APPLY (
    SELECT vs.appointment_id
      FROM vaccination_summary vs
     WHERE vs.person_id = p.person_id
       AND flu_yn = 'Y'
     ORDER BY vs.appointment_date DESC
     FETCH FIRST ROW ONLY
  ) flu
  OUTER APPLY (
    SELECT vs.appointment_id
      FROM vaccination_summary vs
     WHERE vs.person_id = p.person_id
       AND covid_yn = 'Y'
     ORDER BY vs.appointment_date DESC
     FETCH FIRST ROW ONLY
  ) covid
  OUTER APPLY (
    SELECT vs.appointment_id
      FROM vaccination_summary vs
     WHERE vs.person_id = p.person_id
       AND hiv_yn = 'Y'
     ORDER BY vs.appointment_date DESC
     FETCH FIRST ROW ONLY
  ) hiv;

优化方案：窗口函数结合Unpivot/Pivot #

这种方式只需扫描一次表，效率更高，且完全兼容Oracle 11g：

完整查询语句 #

WITH unpivoted_data AS (
  -- 将多布尔列转为行，仅保留YN='Y'的记录
  SELECT
    vs.person_id,
    vs.appointment_id,
    vs.appointment_date,
    vaccine_type
  FROM vaccination_summary vs
  UNPIVOT (
    yn FOR vaccine_type IN (
      mmr_yn AS 'MMR',
      flu_yn AS 'FLU',
      covid_yn AS 'COVID',
      hiv_yn AS 'HIV'
    )
  )
  WHERE yn = 'Y'
),
ranked_data AS (
  -- 按用户和疫苗类型分组，取最新日期的记录
  SELECT
    person_id,
    appointment_id,
    vaccine_type,
    ROW_NUMBER() OVER (PARTITION BY person_id, vaccine_type ORDER BY appointment_date DESC) rn
  FROM unpivoted_data
)
-- 将结果转回列结构，关联所有用户（无记录则显示NULL）
SELECT
  p.person_id,
  pvt.MMR AS mmr_appointment_id,
  pvt.FLU AS flu_appointment_id,
  pvt.COVID AS covid_appointment_id,
  pvt.HIV AS hiv_appointment_id
FROM person p
LEFT JOIN (
  SELECT person_id, vaccine_type, appointment_id
  FROM ranked_data
  WHERE rn = 1
) src
PIVOT (
  MAX(appointment_id) FOR vaccine_type IN ('MMR' AS MMR, 'FLU' AS FLU, 'COVID' AS COVID, 'HIV' AS HIV)
) pvt ON p.person_id = pvt.person_id
ORDER BY p.person_id;

方案优势 #

• 高效性：仅扫描vaccination_summary表一次，避免了多次子查询的重复扫描
• 兼容性：使用Oracle 11g原生支持的UNPIVOT/PIVOT和窗口函数，无需依赖12c+的新特性
• 扩展性：新增布尔列时，只需修改Unpivot和Pivot中的疫苗类型列表，无需新增子查询

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