题解 | 更新员工信息表

--将两个表上下连接
--日期一样的问题，将两个表新加标记1,2，先以时间排序，再以标记排序
WITH t AS (
    SELECT 
        EMPLOYEE_ID, 
        POSITION, 
        LAST_UPDATE_DT,
        1 AS source  
    FROM EMPLOYEE_INFO
    UNION ALL
    SELECT 
        EMPLOYEE_ID, 
        NEW_POSITION AS POSITION, 
        UPDATE_DT AS LAST_UPDATE_DT,
        2 AS source  
    FROM EMPLOYEE_UPDATE
),
ranked AS (
    SELECT 
        EMPLOYEE_ID,
        POSITION,
        LAST_UPDATE_DT,
        ROW_NUMBER() OVER (
            PARTITION BY EMPLOYEE_ID 
            ORDER BY LAST_UPDATE_DT DESC, source ASC
        ) AS rn
    FROM t
)
SELECT 
    EMPLOYEE_ID, 
    POSITION, 
    LAST_UPDATE_DT
FROM ranked
WHERE rn = 1
ORDER BY EMPLOYEE_ID;