MySQL查询构建问题：基于新职位start_date设置上一职位end_date

我来帮你搞定这个职位end_date更新的需求！你的核心需求是根据员工下一级职位的start_date，来设置当前职位的end_date（减1天），而且只针对House job officer和Medical officer这两个上一级职位，最后一个职位Doctor不需要设置end_date。下面给你两种解决方案，分别适配不同版本的MySQL：

方案一：用窗口函数（MySQL 8.0及以上推荐） #

窗口函数LEAD()可以非常方便地获取同员工下一个职位的start_date，逻辑清晰且不易出错：

WITH employee_next_dates AS (
    SELECT 
        id,
        designation,
        start_date,
        -- 按员工分组，按入职日期排序，获取下一个职位的开始日期
        LEAD(start_date) OVER (PARTITION BY id ORDER BY start_date) AS next_start_date
    FROM employee
)
UPDATE employee e
JOIN employee_next_dates end_dates 
    ON e.id = end_dates.id 
    AND e.designation = end_dates.designation
SET e.end_date = DATE_SUB(end_dates.next_start_date, INTERVAL 1 DAY)
WHERE 
    -- 只更新有下一级职位的记录
    end_dates.next_start_date IS NOT NULL
    -- 仅针对规则适用的上一级职位
    AND e.designation IN ('house job officer', 'medical officer');

逻辑说明 #

1. 先用CTEemployee_next_dates为每个员工的每个职位，找到后续晋升职位的start_date；
2. 关联原表和CTE，把符合条件的职位的end_date设置为下一个职位start_date减1天；
3. 通过WHERE条件过滤掉没有下一级的职位（也就是Doctor），同时只更新规则指定的两个上一级职位。

方案二：自连接方式（适配MySQL 5.x版本） #

如果你的MySQL版本不支持窗口函数，可以用自连接来匹配上下级职位：

UPDATE employee e1
JOIN employee e2 
    ON e1.id = e2.id 
    AND (
        -- 匹配House job officer的下一级Medical officer
        (e1.designation = 'house job officer' AND e2.designation = 'medical officer')
        -- 匹配Medical officer的下一级Doctor
        OR (e1.designation = 'medical officer' AND e2.designation = 'doctor')
    )
SET e1.end_date = DATE_SUB(e2.start_date, INTERVAL 1 DAY);

逻辑说明 #

直接通过自连接，把同一个员工的上下级职位关联起来，然后直接更新上一级职位的end_date为下一级的start_date减1天，这种方式适合数据结构固定（每个员工的职位严格按层级递进）的场景。

验证结果 #

执行上述任意一个语句后，你的表数据会变成期望的样子：

id | designation | start_date | end_date
101 | house job officer | 2022-01-19 | 2022-03-18
101 | medical officer | 2022-03-19 | 2022-05-01
101 | doctor | 2022-05-02 |

内容的提问来源于stack exchange，提问作者abacus-A