MySQL 5.5.28如何将连续日期天数合并为短横线格式？

当然可以实现！虽然MySQL 5.5没有窗口函数这类高级特性，但我们可以利用用户自定义变量来对连续日期进行分组，最终将连续天数合并成x-y的格式。下面是具体的实现步骤：

步骤1：标记连续日期组 #

首先，我们需要给每个连续的日期分配一个分组ID。连续日期的分组ID保持一致，当日期断开时分组ID递增。通过比较当前日期和前一个日期的差值来实现：

SELECT
    day(date) AS day_num,
    month(date) AS month_num,
    year(date) AS year_num,
    @group_id := IF(@prev_date = date - INTERVAL 1 DAY, @group_id, @group_id + 1) AS group_id,
    @prev_date := date AS current_date
FROM
    tbl_contract_dates,
    (SELECT @prev_date := NULL, @group_id := 0) AS init_vars
WHERE
    contract_id = 34
ORDER BY
    date;

这段SQL会输出每个日期对应的天数、月份、年份，以及它所属的连续分组ID。

步骤2：按分组和年月聚合 #

接下来，按年月和分组ID聚合，得到每个分组的起始和结束天数，将单天保留为数字，连续天数转换为起始-结束的格式：

SELECT
    month_num,
    year_num,
    group_id,
    MIN(day_num) AS start_day,
    CASE WHEN MIN(day_num) = MAX(day_num) THEN CAST(MIN(day_num) AS CHAR)
         ELSE CONCAT(MIN(day_num), '-', MAX(day_num)) END AS day_range
FROM (
    SELECT
        day(date) AS day_num,
        month(date) AS month_num,
        year(date) AS year_num,
        @group_id := IF(@prev_date = date - INTERVAL 1 DAY, @group_id, @group_id + 1) AS group_id,
        @prev_date := date AS current_date
    FROM
        tbl_contract_dates,
        (SELECT @prev_date := NULL, @group_id := 0) AS init_vars
    WHERE
        contract_id = 34
    ORDER BY
        date
) AS grouped_days
GROUP BY
    month_num, year_num, group_id
ORDER BY
    year_num, month_num, group_id;

这一步会把每个连续分组转换成单个天数或天数范围。

步骤3：合并同一年月的所有范围 #

最后，用GROUP_CONCAT把同一年月的所有天数范围合并成最终的字符串，同时格式化月份为英文全称：

SELECT
    GROUP_CONCAT(day_range SEPARATOR ', ') AS days,
    DATE_FORMAT(STR_TO_DATE(CONCAT(year_num, '-', month_num, '-01'), '%Y-%m-%d'), '%M') AS month,
    year_num AS year
FROM (
    SELECT
        month_num,
        year_num,
        group_id,
        MIN(day_num) AS start_day,
        CASE WHEN MIN(day_num) = MAX(day_num) THEN CAST(MIN(day_num) AS CHAR)
             ELSE CONCAT(MIN(day_num), '-', MAX(day_num)) END AS day_range
    FROM (
        SELECT
            day(date) AS day_num,
            month(date) AS month_num,
            year(date) AS year_num,
            @group_id := IF(@prev_date = date - INTERVAL 1 DAY, @group_id, @group_id + 1) AS group_id,
            @prev_date := date AS current_date
        FROM
            tbl_contract_dates,
            (SELECT @prev_date := NULL, @group_id := 0) AS init_vars
        WHERE
            contract_id = 34
        ORDER BY
            date
    ) AS grouped_days
    GROUP BY
        month_num, year_num, group_id
) AS range_days
GROUP BY
    year_num, month_num
ORDER BY
    year_num, month_num;

验证结果 #

运行上述SQL后，你会得到完全符合需求的输出：

+--------------+-----------+------+
| days         | month     | year |
+--------------+-----------+------+
| 8, 10-12, 16 | August    | 2020 |
| 20, 27-30    | September | 2020 |
| 1-2          | October   | 2020 |
+--------------+-----------+------+

需要注意的是，在MySQL 5.5中，用户变量的初始化和执行顺序非常重要，必须确保变量在使用前被正确初始化，并且ORDER BY子句能保证日期的顺序，这样分组逻辑才会生效。

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