如何在Oracle中按员工最大生效日期获取单条记录

原始表数据 #

期望输出 #

解决方案 #

方法1：子查询关联 #

先通过子查询找出每个员工的最大生效日期，再关联原表获取完整记录：

SELECT e.*
FROM EMPLOYEE e
INNER JOIN (
    SELECT "Emp Id", MAX("Eff Date") AS max_eff_date
    FROM EMPLOYEE
    GROUP BY "Emp Id"
) m ON e."Emp Id" = m."Emp Id" AND e."Eff Date" = m.max_eff_date;

说明：子查询按Emp Id分组计算每个员工的最大生效日期，再和原表关联匹配，得到对应完整记录。

方法2：窗口函数ROW_NUMBER() #

用Oracle窗口函数给每个员工的记录按生效日期降序排名，筛选排名为1的记录：

SELECT "Emp Id", "Emp Name", "Eff Date"
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY "Emp Id" ORDER BY "Eff Date" DESC) AS rn
    FROM EMPLOYEE
)
WHERE rn = 1;

说明：PARTITION BY "Emp Id"按员工ID分组，ORDER BY "Eff Date" DESC让组内最新日期的记录排在最前，ROW_NUMBER()为每条记录分配排名，最后取排名1的就是每个员工的最新记录。

方法3：Oracle特有的KEEP子句 #

利用Oracle聚合函数结合KEEP子句直接获取目标记录：

SELECT 
    "Emp Id",
    MAX("Emp Name") KEEP(DENSE_RANK LAST ORDER BY "Eff Date") AS "Emp Name",
    MAX("Eff Date") AS "Eff Date"
FROM EMPLOYEE
GROUP BY "Emp Id";

说明：KEEP(DENSE_RANK LAST ORDER BY "Eff Date")保留每个组中生效日期最大的行，再通过MAX函数取出对应的员工姓名（同一员工ID的姓名通常一致，用MAX/MIN均可）。

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