MySQL中如何计算连续行日期差并更新对应表列？

嘿，这个需求很常见，就是要计算同一个医生名下连续就诊患者的orderUnRO said Starblob， By费Anal的YN Visits对吧？给你两种主流实现方法，适配不同版本的MySQL：

方法一：用窗口函数（MySQL 8.0+ 推荐） #

MySQL 8.0及以上支持窗口函数，用LAG()能直接获取同一个医生的上一位患者就诊时间，轻松计算时间差。

第一步：先验证计算结果（避免直接更新出错） #

先跑个查询看看时间差是否符合预期：

SELECT 
    ID,
    DOCTOR_ID,
    PATIENT_IN_TIME,
    -- 计算当前行与上一行的时间差（分钟）
    TIMESTAMPDIFF(MINUTE, 
                  LAG(PATIENT_IN_TIME) OVER (PARTITION BY DOCTOR_ID ORDER BY PATIENT_IN_TIME), 
                  PATIENT_IN_TIME) AS AVG_CHECKUP_TIME
FROM your_table_name;

• PARTITION BY DOCTOR_ID：确保只计算同一个医生的连续患者时间差，不会跨医生乱算
• ORDER BY PATIENT_IN_TIME：按就诊时间排序取上一行，比按ID排序更靠谱（毕竟偶尔会有ID自增但就诊时间乱序的情况）
• 第一行的结果会是NULL，因为没有上一位患者，这完全符合逻辑

第二步：执行更新操作 #

确认结果没问题后，用CTE（公共表表达式）来更新表：

WITH time_diff_calculation AS (
    SELECT 
        ID,
        TIMESTAMPDIFF(MINUTE, 
                      LAG(PATIENT_IN_TIME) OVER (PARTITION BY DOCTOR_ID ORDER BY PATIENT_IN_TIME), 
                      PATIENT_IN_TIME) AS diff_minutes
    FROM your_table_name
)
UPDATE your_table_name t
JOIN time_diff_calculation td ON t.ID = td.ID
SET t.AVG_CHECKUP_TIME = td.diff_minutes;

用你给的示例数据执行后，第二行的AVG_CHECKUP_TIME会变成6（02:56:37减去02:49:51是6分钟46秒，TIMESTAMPDIFF取整分钟就是6）。

方法二：自连接实现（MySQL 5.x 兼容） #

如果你的MySQL版本低于8.0，不支持窗口函数，可以用自连接的方式找到上一行数据：

UPDATE your_table_name t1
LEFT JOIN your_table_name t2 
    ON t1.DOCTOR_ID = t2.DOCTOR_ID 
    AND t2.ID = (SELECT MAX(ID) FROM your_table_name WHERE DOCTOR_ID = t1.DOCTOR_ID AND ID < t1.ID)
SET t1.AVG_CHECKUP_TIME = TIMESTAMPDIFF(MINUTE, t2.PATIENT_IN_TIME, t1.PATIENT_IN_TIME);

这个逻辑是通过子查询找到同一个医生下ID比当前行小的最大ID（也就是前一行），再计算时间差。

小提醒 #

• 记得把your_table_name替换成你实际的表名
• 执行更新前一定要先用SELECT语句验证结果，避免误操作
• 如果表数据量很大，窗口函数的效率会比自连接高不少

内容的提问来源于stack exchange，提问作者vs_lala