题解 | 更新员工信息表

WITH
    temp AS (
        SELECT
            EMPLOYEE_ID,
            UPDATE_DT,
            NEW_POSITION
        FROM
            (
                SELECT
                    EMPLOYEE_ID,
                    UPDATE_DT,
                    NEW_POSITION,
                    ROW_NUMBER() OVER (
                        PARTITION BY
                            EMPLOYEE_ID
                        ORDER BY
                            UPDATE_DT DESC
                    ) AS rk
                FROM
                    EMPLOYEE_UPDATE
            ) AS a
        WHERE
            rk = 1
    )
SELECT
    b.EMPLOYEE_ID,
    COALESCE(t.NEW_POSITION, b.POSITION) AS POSITION,
    COALESCE(t.UPDATE_DT, b.LAST_UPDATE_DT) AS LAST_UPDATE_DT
FROM
    EMPLOYEE_INFO AS b
    LEFT JOIN temp AS t ON b.EMPLOYEE_ID = t.EMPLOYEE_ID
    AND b.LAST_UPDATE_DT < t.UPDATE_DT
ORDER BY
    b.EMPLOYEE_ID ASC

我的方法有点小麻烦,先用ROW_NUMBER窗口函数排除职位更新表的最近一次更新，然后再和员工表LEFT JOIN并且连结条件是要更新表的日期大于员工表，这样子要是不大于的话就会是NULL,再用COALESCE将NULL值用员工表中的正确数据替代就好了