基于SQL Islands and Gaps技术查询车辆可用时段

我们正尝试简化公司内部的车辆预订流程。目前采用1小时时段预订模式，用户若需全日用车，得重复多次操作。现在计划设置车辆可用周期，允许用户选择任意时长预订，所以需要借助SQL Gaps and Islands技术，从预订会话和预订记录里找出车辆未被预订的可用时段。

示例表结构与测试数据 #

DECLARE @BookingSession TABLE
(
    id_BookingSession INT
    ,SessionStart DATETIME
    ,SessionEnd DATETIME
);

INSERT INTO @BookingSession (id_BookingSession,SessionStart,SessionEnd)
VALUES
(
    1,'20260507 09:00','20260507 17:00'
);

DECLARE @Bookings TABLE
(
    id_BookingSession INT
    ,id_BookingSession_Slot INT
    ,TimeFrom DATETIME
    ,TimeUntil DATETIME
);

--生成部分预订记录
INSERT INTO @Bookings (id_BookingSession,id_BookingSession_Slot,TimeFrom,TimeUntil) 
VALUES
(1,90,'20260507 10:05','20260507 10:45')  -- 40分钟预订
,(1,91,'20260507 11:05','20260507 11:30') -- 25分钟预订
,(1,93,'20260507 11:30','20260507 12:30') -- 60分钟预订（与上一条无间隔）;

SELECT * FROM @BookingSession;
SELECT * FROM @bookings;

期望输出 #

解决方案 #

利用Gaps and Islands思路，我们可以把预订会话的时间范围和已有的预订记录整合，定位出所有未被占用的时段：

WITH BookingIntervals AS (
    -- 会话开始到第一个预订的间隔
    SELECT 
        bs.id_BookingSession,
        bs.SessionStart AS IntervalStart,
        MIN(b.TimeFrom) AS IntervalEnd
    FROM @BookingSession bs
    LEFT JOIN @Bookings b ON bs.id_BookingSession = b.id_BookingSession
    GROUP BY bs.id_BookingSession, bs.SessionStart

    UNION ALL

    -- 相邻两个预订之间的间隔
    SELECT 
        b1.id_BookingSession,
        b1.TimeUntil AS IntervalStart,
        MIN(b2.TimeFrom) AS IntervalEnd
    FROM @Bookings b1
    JOIN @Bookings b2 ON b1.id_BookingSession = b2.id_BookingSession 
        AND b2.TimeFrom > b1.TimeUntil
    GROUP BY b1.id_BookingSession, b1.TimeUntil

    UNION ALL

    -- 最后一个预订结束到会话结束的间隔
    SELECT 
        bs.id_BookingSession,
        MAX(b.TimeUntil) AS IntervalStart,
        bs.SessionEnd AS IntervalEnd
    FROM @BookingSession bs
    LEFT JOIN @Bookings b ON bs.id_BookingSession = b.id_BookingSession
    GROUP BY bs.id_BookingSession, bs.SessionEnd
)
-- 筛选有效时段并格式化输出
SELECT 
    FORMAT(IntervalStart, 'HH:mm') + ' - ' + FORMAT(IntervalEnd, 'HH:mm') + ' Available' AS Result
FROM BookingIntervals
WHERE IntervalStart < IntervalEnd
ORDER BY IntervalStart;

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