如何在单表中结合特定条件使用DATEDIFF函数计算天数

原数据表 #

预期结果 #

计算规则 #

• 同ID下当前arrival记录之后存在其他arrival记录：天数为两次arrival日期的间隔
• 同ID下当前arrival记录之后存在departure记录：天数为arrival与departure日期的间隔
• 同ID下当前arrival记录无后续记录：天数为arrival日期与当前日期的间隔

解决方案 #

方案1：子查询兼容低版本数据库 #

适用于不支持窗口函数的数据库版本（如MySQL 5.x）：

SELECT 
    t1.IDno,
    t1.name,
    t1.date,
    t1.status,
    DATEDIFF(
        COALESCE(
            -- 取同ID下晚于当前日期的最早记录日期
            (SELECT MIN(t2.date) FROM mytable t2 WHERE t2.IDno = t1.IDno AND t2.date > t1.date),
            NOW() -- 无后续记录则用当前日期
        ),
        t1.date
    ) AS days
FROM mytable t1
WHERE t1.status = 'arrival'
ORDER BY t1.IDno, t1.date;

方案2：窗口函数高效实现 #

适用于支持窗口函数的数据库版本（如MySQL 8+、PostgreSQL等）：

SELECT 
    IDno,
    name,
    date,
    status,
    DATEDIFF(
        COALESCE(next_date, NOW()),
        date
    ) AS days
FROM (
    SELECT 
        *,
        -- 按ID分组，获取当前记录的下一个后续日期
        LEAD(date) OVER (PARTITION BY IDno ORDER BY date) AS next_date
    FROM mytable
    WHERE status = 'arrival'
) AS sub_query
ORDER BY IDno, date;

逻辑说明 #

1. 获取后续日期：两种方案都会找到当前arrival记录之后，同ID的最早后续记录日期（无论是arrival还是departure），满足前两个规则的计算需求。
2. 处理无后续记录：用COALESCE函数判断，若没有后续记录则用NOW()替代，计算到当前日期的间隔。
3. 日期差计算：通过DATEDIFF函数计算目标日期与当前arrival日期的天数间隔。

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