请求编写Oracle SQL查询：获取各REF_NUM的第二高DATE对应数据

需求说明 #

从目标表中，为每个唯一的REF_NUM获取对应的ID和第二高的DATE记录，每个REF_NUM可能包含多条日期记录。

样本数据 #

REF_NUM ID  DATE
SIM1    1   12-Oct-22
SIM1    2   10-Oct-22
SIM2    3   15-Oct-22
SIM2    4   14-Oct-22
SIM3    5   08-Oct-22
SIM3    6   02-Oct-22
SIM4    7   08-Oct-22
SIM4    8   10-Oct-22

期望输出 #

REF_NUM ID  DATE
SIM1    2   10-Oct-22
SIM2    4   14-Oct-22
SIM3    6   02-Oct-22
SIM4    7   08-Oct-22

方案1：使用ROW_NUMBER()窗口函数（推荐） #

利用Oracle窗口函数按分组排序，直接筛选出第二高日期的记录，逻辑清晰且高效。

SELECT REF_NUM, ID, DATE
FROM (
    SELECT 
        REF_NUM, 
        ID, 
        DATE,
        -- 按REF_NUM分组，组内DATE降序排序，生成序号
        ROW_NUMBER() OVER (PARTITION BY REF_NUM ORDER BY DATE DESC) AS rn
    FROM your_table_name
) t
-- 筛选序号为2的记录，即第二高日期
WHERE rn = 2;

补充说明 #

• 如果同一REF_NUM下存在多个日期相同的第二高记录，ROW_NUMBER()会随机分配序号，此时可改用DENSE_RANK()保留所有符合条件的记录：

SELECT REF_NUM, ID, DATE
FROM (
    SELECT 
        REF_NUM, 
        ID, 
        DATE,
        DENSE_RANK() OVER (PARTITION BY REF_NUM ORDER BY DATE DESC) AS rn
    FROM your_table_name
) t
WHERE rn = 2;

方案2：使用子查询筛选第二高日期 #

不依赖窗口函数的传统写法，通过嵌套子查询定位每个分组的第二高日期，再关联原表获取对应记录。

SELECT t.REF_NUM, t.ID, t.DATE
FROM your_table_name t
WHERE t.DATE = (
    -- 找到当前REF_NUM下，小于最高日期的最大日期（即第二高日期）
    SELECT MAX(DATE)
    FROM your_table_name
    WHERE REF_NUM = t.REF_NUM
      AND DATE < (SELECT MAX(DATE) FROM your_table_name WHERE REF_NUM = t.REF_NUM)
);

补充说明 #

• 该方案会返回同一REF_NUM下所有日期等于第二高日期的记录，适合需要保留重复第二高日期场景。

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