如何在MySQL/MariaDB中从车位已占用时段计算可用时段？

要反推出指定车位的可用时段，核心是先合并该车位所有重叠/连续的占用时段，再计算总可用范围与这些占用时段之间的间隙。以下是适配MariaDB v10.10.2的存储过程实现：

CREATE PROCEDURE `GetSlotAvailableTimes`(IN slotId INT)
BEGIN
    -- 定义车位总可用时段范围
    SET @total_start = '2023-05-30 15:00:00';
    SET @total_end = '9999-12-30 00:00:00';

    -- 递归CTE合并重叠/连续的占用时段
    WITH RECURSIVE merged_reservations AS (
        -- 基础查询：获取该车位的有效预约时段（过滤无效的空时间和状态）
        SELECT
            slot_id,
            `start` AS occupied_start,
            `end` AS occupied_end
        FROM reservations
        WHERE slot_id = slotId
          AND `start` IS NOT NULL
          AND `end` IS NOT NULL
          AND `status` = 'confirmed' -- 请根据实际业务调整有效状态值
        ORDER BY `start`

        UNION ALL

        -- 递归合并重叠/连续时段
        SELECT
            mr.slot_id,
            mr.occupied_start,
            GREATEST(mr.occupied_end, r.`end`) AS occupied_end
        FROM merged_reservations mr
        JOIN reservations r
            ON mr.slot_id = r.slot_id
            AND r.`start` IS NOT NULL
            AND r.`end` IS NOT NULL
            AND r.`status` = 'confirmed'
            AND r.`start` <= mr.occupied_end
            AND r.`end` > mr.occupied_end
    ),
    -- 去重得到最终不重叠的占用时段
    distinct_merged AS (
        SELECT DISTINCT slot_id, occupied_start, occupied_end
        FROM merged_reservations
        ORDER BY occupied_start
    ),
    -- 为每个占用时段关联上一个时段的结束时间
    ordered_reservations AS (
        SELECT
            occupied_start,
            occupied_end,
            LAG(occupied_end) OVER (ORDER BY occupied_start) AS prev_end
        FROM distinct_merged
    )
    -- 生成所有可用时段
    -- 1. 总可用起始到第一个占用时段的间隙
    SELECT
        @total_start AS available_start,
        occupied_start AS available_end
    FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY occupied_start) AS rn FROM ordered_reservations) t
    WHERE rn = 1 AND occupied_start > @total_start

    UNION ALL

    -- 2. 相邻占用时段之间的间隙
    SELECT
        prev_end AS available_start,
        occupied_start AS available_end
    FROM ordered_reservations
    WHERE prev_end IS NOT NULL AND occupied_start > prev_end

    UNION ALL

    -- 3. 最后一个占用时段到总可用结束的间隙
    SELECT
        occupied_end AS available_start,
        @total_end AS available_end
    FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY occupied_start DESC) AS rn FROM ordered_reservations) t
    WHERE rn = 1 AND occupied_end < @total_end

    -- 4. 无任何占用时段时返回总可用范围
    UNION ALL

    SELECT @total_start AS available_start, @total_end AS available_end
    WHERE NOT EXISTS (SELECT 1 FROM distinct_merged);
END

关键逻辑说明 #

1. 合并重叠时段：通过递归CTE把同一车位的重叠/连续预约时段合并成单个连续时段，避免因多个重叠预约导致可用时段计算错误。
2. 计算间隙：使用窗口函数LAG()获取每个占用时段的上一个时段结束时间，以此计算相邻时段的间隙；同时处理总可用范围首尾的间隙。
3. 边界处理：兼容无任何占用时段的场景，直接返回总可用范围。

使用方式 #

调用存储过程并传入目标车位ID：

CALL GetSlotAvailableTimes(你的车位ID);

注意事项 #

• 请根据实际业务调整status = 'confirmed'的过滤条件，确保只统计有效占用的预约。
• 如果存在start或end为NULL的预约（比如长期占用），需修改逻辑将NULL替换为总可用范围的对应边界值（例如把NULL的end替换为@total_end）。

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