⭐题解 | 更新员工信息表

-- 先找出最新的更新日期
WITH latest_date AS (
    SELECT employee_id, MAX(update_dt) AS max_date
    FROM EMPLOYEE_UPDATE
    GROUP BY employee_id
),
-- 第二个CTE查询最新的岗位
latest_position AS (
    SELECT e.employee_id, e.new_position, d.max_date
    FROM latest_date d
    LEFT JOIN EMPLOYEE_UPDATE e
        ON d.employee_id = e.employee_id
        AND d.max_date = e.update_dt
)

SELECT
    i.employee_id AS EMPLOYEE_ID,
    CASE WHEN lp.max_date > i.last_update_dt THEN lp.new_position ELSE i.position END AS POSITION,
    CASE WHEN lp.max_date > i.last_update_dt THEN lp.max_date ELSE i.last_update_dt END AS LAST_UPDATE_DT
FROM EMPLOYEE_INFO i
LEFT JOIN latest_position lp 
    ON i.employee_id = lp.employee_id
ORDER BY i.employee_id;

这个题这么复杂给我放到中等题里啊.................

首先判断的时候记得用case...when即可。