题解 | 更新员工信息表

SELECT
    EMPLOYEE_ID,
    POSITION,
    LAST_UPDATE_DT
FROM
    (
        SELECT
            EMPLOYEE_INFO.EMPLOYEE_ID,
            (
                CASE
                    WHEN NEW_POSITION IS NULL THEN POSITION
                    ELSE NEW_POSITION
                END
            ) AS POSITION,
            (
                CASE
                    WHEN UPDATE_DT IS NULL THEN LAST_UPDATE_DT
                    ELSE UPDATE_DT
                END
            ) AS LAST_UPDATE_DT,
            ROW_NUMBER() OVER(PARTITION BY EMPLOYEE_INFO.EMPLOYEE_ID ORDER BY UPDATE_DT DESC) AS RANKING
        FROM
            (
                SELECT
                    *
                FROM EMPLOYEE_UPDATE AS EU
                WHERE EXISTS(
                    SELECT 1
                    FROM EMPLOYEE_INFO AS EI
                    WHERE EU.EMPLOYEE_ID=EI.EMPLOYEE_ID AND EU.UPDATE_DT > EI.LAST_UPDATE_DT
                )
            ) AS NEW_POSITION_PART
            RIGHT JOIN EMPLOYEE_INFO ON NEW_POSITION_PART.EMPLOYEE_ID=EMPLOYEE_INFO.EMPLOYEE_ID 
    ) AS RANKING_TB
WHERE RANKING=1

该说不说这大写的表名字段是真抽象