题解 | 更新员工信息表

SELECT
    t.EMPLOYEE_ID,
    CASE WHEN UPDATE_DT > LAST_UPDATE_DT THEN NEW_POSITION
    ELSE POSITION
    END AS POSITION,
    CASE WHEN UPDATE_DT > LAST_UPDATE_DT THEN UPDATE_DT
    ELSE LAST_UPDATE_DT
    END AS LAST_UPDATE_DT
FROM EMPLOYEE_INFO JOIN (
    SELECT
        EMPLOYEE_ID,
        UPDATE_DT,
        NEW_POSITION
    FROM EMPLOYEE_UPDATE
    WHERE (EMPLOYEE_ID, UPDATE_DT) IN (
        SELECT
            ANY_VALUE(EMPLOYEE_ID),
            MAX(UPDATE_DT)
        FROM EMPLOYEE_UPDATE
        GROUP BY EMPLOYEE_ID
    )
) t
ON EMPLOYEE_INFO.EMPLOYEE_ID = t.EMPLOYEE_ID
ORDER BY EMPLOYEE_ID