MySQL中如何计算指定日期范围与表内日期的重叠天数

首先咱们先明确已知条件：leave_management表中有一条请假记录，区间是**'2016-12-11' 至 '2016-12-18'**，需要根据输入的任意日期范围，算出两者的重叠天数，对应你给出的三个场景。

核心逻辑推导 #

要算两个日期区间的重叠天数，关键是先锁定实际重叠的起始和结束日期，再根据这两个日期计算天数：

1. 重叠起始日期：取输入区间的开始日期和请假记录的开始日期里更大的那个（MAX(input_from, leave_from)）
2. 重叠结束日期：取输入区间的结束日期和请假记录的结束日期里更小的那个（MIN(input_to, leave_to)）
3. 判断重叠是否存在：如果重叠起始日期晚于重叠结束日期，说明完全没重叠，天数为0；否则根据重叠区间计算天数。

结合你给出的三个场景，咱们逐一验证：

场景1：输入区间完全在请假记录之前

• 输入：From: '2016-12-7'、To: '2016-12-10'
• 重叠起始：MAX('2016-12-7', '2016-12-11') = '2016-12-11'
• 重叠结束：MIN('2016-12-10', '2016-12-18') = '2016-12-10'
• 因为2016-12-11 > 2016-12-10，所以重叠天数为0，符合预期。

场景2：输入区间完全包含在请假记录内

• 输入：From: '2016-12-16'、To: '2016-12-18'
• 重叠起始：MAX('2016-12-16', '2016-12-11') = '2016-12-16'
• 重叠结束：MIN('2016-12-18', '2016-12-18') = '2016-12-18'
• 按照你给出的输出，重叠天数为2，对应的计算方式是结束日期减开始日期的天数差（DATEDIFF(day, '2016-12-16', '2016-12-18')）。

场景3：输入区间与请假记录尾部重叠

• 输入：From: '2016-12-18'、To: '2016-12-20'
• 重叠起始：MAX('2016-12-18', '2016-12-11') = '2016-12-18'
• 重叠结束：MIN('2016-12-20', '2016-12-18') = '2016-12-18'
• 此时重叠起始和结束是同一天，所以天数为1，符合预期。

通用SQL实现示例 #

如果要在SQL中实现这个逻辑（以MySQL为例），可以用下面的查询语句：

SELECT 
    CASE 
        WHEN MAX(input_from, leave_from) > MIN(input_to, leave_to) THEN 0
        WHEN MAX(input_from, leave_from) = MIN(input_to, leave_to) THEN 1
        ELSE DATEDIFF(MIN(input_to, leave_to), MAX(input_from, leave_from))
    END AS overlap_days
FROM leave_management
WHERE leave_from = '2016-12-11' AND leave_to = '2016-12-18';

注：如果用SQL Server，DATEDIFF语法一致；如果是PostgreSQL，用(MIN(input_to, leave_to) - MAX(input_from, leave_from))::int计算天数差即可。

如果需要适配更通用的“实际包含天数”（比如场景2应该输出3，即包含起始和结束日），只需要把CASE里的ELSE部分改成DATEDIFF(MIN(input_to, leave_to), MAX(input_from, leave_from)) + 1，这样场景2的结果就是3，场景3还是1，场景1还是0。

内容的提问来源于stack exchange，提问作者Mahbubur Rahman Khan