Oracle SQL：如何获取员工最新职位的最早任职日期

需求说明 #

为每位员工返回其最新职位对应的最早任职日期，每位员工仅返回一条结果。

原始数据（表名假设为EMP_POSITIONS） #

预期结果 #

解决方案 #

方法一：分步筛选最新职位再聚合 #

通过CTE先锁定每位员工的最新职位，再关联原表获取该职位的最早任职日期：

WITH latest_pos AS (
    SELECT 
        EMPLOYEE,
        POSITIONCODE,
        ROW_NUMBER() OVER (PARTITION BY EMPLOYEE ORDER BY "DATE" DESC) AS rn
    FROM EMP_POSITIONS
),
employee_latest_pos AS (
    SELECT EMPLOYEE, POSITIONCODE
    FROM latest_pos
    WHERE rn = 1
)
SELECT 
    elp.EMPLOYEE,
    elp.POSITIONCODE,
    MIN(ep."DATE") AS "DATE"
FROM employee_latest_pos elp
JOIN EMP_POSITIONS ep 
    ON elp.EMPLOYEE = ep.EMPLOYEE 
    AND elp.POSITIONCODE = ep.POSITIONCODE
GROUP BY elp.EMPLOYEE, elp.POSITIONCODE;

方法二：单查询嵌套窗口函数 #

用FIRST_VALUE直接标记每位员工的最新职位，再筛选后聚合取最小日期：

SELECT 
    EMPLOYEE,
    POSITIONCODE,
    MIN("DATE") AS "DATE"
FROM (
    SELECT 
        EMPLOYEE,
        POSITIONCODE,
        "DATE",
        FIRST_VALUE(POSITIONCODE) OVER (PARTITION BY EMPLOYEE ORDER BY "DATE" DESC) AS latest_position
    FROM EMP_POSITIONS
)
WHERE POSITIONCODE = latest_position
GROUP BY EMPLOYEE, POSITIONCODE;

说明 #

之前用GROUP BY+MIN()直接查询时，会返回员工所有职位的最早日期，无法锁定最新职位。上述两种方法先通过窗口函数确定每位员工的当前最新职位，再针对该职位计算最早任职日期，确保每位员工仅返回一条结果。

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