给定一个包含4列或更多列的“汇总”表：
(ID1, ID2, SortOrder, BooleanYN1, BooleanYN2)
对于ID1的每个值，我希望ID2的值对应于具有SortOrder的最高值的BooleanYN{n} = Y行，如果存在任何这样的记录。
范例：
| ID1| ID2| sortOrder|布尔YN 1|布尔YN 2|
| --|--|--|--|--|
| 1 | 10 | 1 |Y| N|
| 1 | 20 | 2 |N| N|
| 1 | 30 | 3 |Y| N|
| 1 | 40 | 4 |N| N|
我正在寻找(1, 30, NULL)，因为ID1 = 1的“最近”（基于SortOrder）行具有BooleanYN1 = 'Y'，是具有ID2 = 30的行，并且没有BooleanYN2 = 'Y'的行。
我在这里用示例数据构建了一个SQL Fiddle：
http://sqlfiddle.com/#!4/a5bc5/6
此查询在Fiddle中不起作用，因为Oracle 11 g不支持OUTER APPLY，但它在Oracle 19中起作用。

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;

它看起来非常不优雅和低效。
当然，一定有更好的方法来做这件事与窗口功能.或者也许un-pivoting，然后重新pivoting？