如何获取emp_sal表中按日期降序的第3条或最旧员工薪资记录？

需求说明 #

按change_date降序获取每位员工的第3条薪资记录；若员工的记录数不足3条，则取其最旧的记录（有2条则取第2条，仅1条则取该条）。

emp_sal表数据 #

尝试的SQL语句 #

select * from 
(select e.*, row_number() over (partition by empid order by change_date desc) as rn
  from emp_sal e)
where rn = 3; -- 不确定还需要添加什么条件

预期输出 #

解决方案 #

需要在子查询中新增统计每个员工总记录数的字段，结合行号来筛选目标记录：

SELECT empid, change_date, salary
FROM (
    SELECT 
        e.*,
        ROW_NUMBER() OVER (PARTITION BY empid ORDER BY change_date DESC) AS rn,
        COUNT(*) OVER (PARTITION BY empid) AS total_cnt
    FROM emp_sal e
) t
WHERE rn = 3 OR (total_cnt < 3 AND rn = total_cnt);

逻辑说明 #

1. ROW_NUMBER() OVER (PARTITION BY empid ORDER BY change_date DESC)：按员工分组，薪资记录按日期降序排序，每条记录得到对应的行号rn。
2. COUNT(*) OVER (PARTITION BY empid)：统计每个员工的总薪资记录数total_cnt。
3. 外层筛选条件：
   • 当员工记录数≥3时，取行号为3的记录（即降序后的第3条）；
   • 当员工记录数<3时，取行号等于总记录数的记录（即降序排序的最后一条，也就是最旧的薪资记录）。

内容的提问来源于stack exchange，提问作者ORA-01017