题解 | 更新员工信息表
更新员工信息表
https://www.nowcoder.com/practice/1eb20d4bf7c5443da7b84105372c9070
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;