题解 | 更新员工信息表

WITH
t1 AS (     -- 自连接所有新老职位信息
SELECT
    EMPLOYEE_ID,
    LAST_UPDATE_DT AS sj,
    POSITION AS zw
FROM EMPLOYEE_INFO

UNION ALL

SELECT 
    EMPLOYEE_ID,
    UPDATE_DT AS sj,
    NEW_POSITION AS zw
FROM EMPLOYEE_UPDATE
),

t2 AS (     -- 使用窗口函数按照更新时间从后往前标注职位排序
SELECT
    EMPLOYEE_ID,sj,zw,
    ROW_NUMBER() OVER(PARTITION BY EMPLOYEE_ID ORDER BY sj DESC) AS rk
FROM t1
)

SELECT      -- 仅显示最新职位
    EMPLOYEE_ID,
    zw AS POSITION,
    sj AS LAST_UPDATE_DT
FROM t2
WHERE rk = 1
ORDER BY EMPLOYEE_ID;