如何在MySQL和PHP中计算同一ID下多段日期的总天数？

首先明确你的需求：针对包含start_date、end_date、id的表，按id统计所有不重复的日期天数，需要正确处理日期段的重叠或间隔情况。

先看你的原始表数据：

start_date  end_date    id
-----------------------------
2018-01-01  2018-01-01  5
2018-01-03  2018-01-03  5
2018-01-03  2018-01-08  5
2018-01-06  2018-01-07  7
2018-01-07  2018-01-07  7
2018-01-09  2018-01-11  7
2018-01-02  2018-01-02  8
2018-01-02  2018-01-04  8
2018-01-08  2018-01-08  9

目标输出是：

total_days  id
----------------
7           5
5           7
3           8
1           9

下面提供两种可行的解决方案：

方法一：使用递归CTE拆分日期（MySQL 8.0+） #

这种方法的思路是把每个日期段拆成单独的日期，然后按id去重统计天数，适合数据量不大的场景。

WITH RECURSIVE date_range AS (
    -- 初始查询：取出每个记录的start_date
    SELECT id, start_date AS day, end_date
    FROM your_table_name
    UNION ALL
    -- 递归生成后续日期，直到达到end_date
    SELECT id, DATE_ADD(day, INTERVAL 1 DAY), end_date
    FROM date_range
    WHERE day < end_date
)
SELECT COUNT(DISTINCT day) AS total_days, id
FROM date_range
GROUP BY id
ORDER BY id;

解释： #

1. 递归CTE的初始部分先取出每条记录的start_date作为起始日期，同时保留end_date用于终止递归；
2. 递归部分每天加1天，直到日期等于end_date；
3. 最后按id分组，统计去重后的日期数量，就是该id的总不重复天数。

方法二：合并重叠/连续日期区间（更高效，适合大数据量） #

如果你的表数据量很大，拆分日期会影响性能，这种方法先合并每个id下的重叠或连续区间，再计算每个合并后区间的天数总和。

WITH ordered_dates AS (
    -- 按id和start_date排序，给每个id内的记录编号
    SELECT 
        id,
        start_date,
        end_date,
        -- 标记是否是新的区间：当前start_date > 之前最大的end_date则为1，否则为0
        SUM(CASE WHEN start_date > COALESCE(LAG(end_date) OVER (PARTITION BY id ORDER BY start_date), '1970-01-01') THEN 1 ELSE 0 END) OVER (PARTITION BY id ORDER BY start_date) AS group_id
    FROM your_table_name
),
merged_ranges AS (
    -- 按id和group_id合并区间，取最小start_date和最大end_date
    SELECT 
        id,
        MIN(start_date) AS merged_start,
        MAX(end_date) AS merged_end
    FROM ordered_dates
    GROUP BY id, group_id
)
-- 计算每个合并区间的天数，再求和
SELECT 
    SUM(DATEDIFF(merged_end, merged_start) + 1) AS total_days,
    id
FROM merged_ranges
GROUP BY id
ORDER BY id;

解释： #

1. ordered_dates CTE：按id分组、start_date排序，用LAG()函数获取上一条记录的end_date，判断当前记录的start_date是否超过上一个区间的end_date，以此标记新的区间组；
2. merged_ranges CTE：按id和区间组group_id合并，得到每个组的起始和结束日期；
3. 最后计算每个合并区间的天数（DATEDIFF(end, start)+1是因为包含首尾日期），再按id求和得到总天数。

这两种方法都能得到你想要的结果，你可以根据自己的MySQL版本和数据量选择合适的方案。

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