PostgreSQL按用户需求返回会议室可用预订时间的SQL实现咨询

嘿，针对你遇到的会议室预订查询问题，我整理了一个基于PostgreSQL的完整解决方案，刚好覆盖你提到的三个场景，咱们一步步来看：

先明确问题背景 #

你有个rooms表存预订信息，结构是ID、Name、Type、StartTime、EndTime，现有数据如下（注意其中有一条Room02的预订结束时间早于开始时间，我默认是输入笔误，后面会处理这种无效记录）：

01 Room01 1 8:00:00 8:30:00
02 Room02 2 9:00:00 9:30:00
01 Room01 1 9:00:00 9:30:00
02 Room02 2 10:00:00 9:00:00
01 Room01 1 11:00:00 11:30:00
02 Room02 2 13:00:00 13:30:00

需要实现三个核心场景：

• 场景1：用户订8:00-8:30，Room01已被占，返回空闲的Room02该时段
• 场景2：用户订5:00-5:30，两个会议室都空，返回任意一个的该时段
• 场景3：用户订9:00-9:30，两个都被占，返回最接近的可用时段（比如Room01的9:30-10:00）

直接上可运行的SQL方案 #

我用CTE（公共表表达式）把逻辑拆成了几个清晰的部分，方便你理解和修改：

WITH user_request AS (
    -- 这里定义用户的预订请求，改这里就能测试不同场景
    SELECT 
        '8:00:00'::time AS req_start,
        '8:30:00'::time AS req_end,
        '00:30:00'::interval AS req_duration
),
valid_bookings AS (
    -- 先过滤掉无效预订（结束时间早于开始时间的，避免干扰判断）
    SELECT * FROM rooms
    WHERE endtime > starttime
),
available_rooms AS (
    -- 找出当前请求时段完全空闲的会议室
    SELECT 
        r.id,
        r.name,
        r.type,
        ur.req_start AS starttime,
        ur.req_end AS endtime
    FROM user_request ur
    -- 先拿到所有会议室的基础信息
    CROSS JOIN (SELECT DISTINCT id, name, type FROM rooms) r
    -- 左连接现有预订，判断是否有时间冲突
    LEFT JOIN valid_bookings b 
        ON r.id = b.id
        AND (ur.req_start < b.endtime AND ur.req_end > b.starttime)
    -- 左连接后没有匹配到预订的，就是空闲的
    WHERE b.id IS NULL
),
room_next_available AS (
    -- 计算每个会议室最早的可用时段（在用户请求结束时间之后）
    SELECT 
        r.id,
        r.name,
        r.type,
        -- 最早可用开始时间：要么是用户要的结束时间，要么是该会议室最后一个预订的结束时间
        GREATEST(ur.req_end, COALESCE(MAX(b.endtime), '00:00:00'::time)) AS starttime,
        -- 结束时间就是开始时间加预订时长
        GREATEST(ur.req_end, COALESCE(MAX(b.endtime), '00:00:00'::time)) + ur.req_duration AS endtime,
        -- 计算和用户请求时间的差值，用来找最接近的
        EXTRACT(EPOCH FROM (GREATEST(ur.req_end, COALESCE(MAX(b.endtime), '00:00:00'::time)) - ur.req_start)) AS time_diff
    FROM user_request ur
    CROSS JOIN (SELECT DISTINCT id, name, type FROM rooms) r
    LEFT JOIN valid_bookings b ON r.id = b.id
    GROUP BY r.id, r.name, r.type, ur.req_start, ur.req_end, ur.req_duration
)
-- 优先级逻辑：先返回空闲会议室，没有的话返回最接近的可用时段
SELECT id, name, type, starttime, endtime
FROM available_rooms
UNION ALL
SELECT id, name, type, starttime, endtime
FROM room_next_available
WHERE NOT EXISTS (SELECT 1 FROM available_rooms)
-- 空闲的优先，非空闲的按时间差从小到大排，取第一个
ORDER BY time_diff NULLS FIRST
LIMIT 1;

逻辑拆解，帮你理解每一步 #

1. user_request：这部分是用户的预订参数，你只要改这里的时间，就能测试三个场景，非常方便
2. valid_bookings：过滤掉那些结束时间比开始时间早的无效记录，避免这些数据干扰冲突判断
3. available_rooms：通过左连接判断会议室在用户请求的时段有没有冲突——如果左连接后找不到对应的预订记录，就说明这个会议室在该时段是空闲的，直接返回用户要的时段
4. room_next_available：当所有会议室都被占时，计算每个会议室最早能订的时间：取用户请求的结束时间和该会议室最后一个预订的结束时间的较大值作为开始时间，加上预订时长就是结束时间，同时计算这个时段和用户请求时间的差值，方便排序找最接近的
5. 最后通过UNION ALL和NOT EXISTS实现优先级：如果有空闲会议室就返回，没有的话就返回时间差最小的那个可用时段，LIMIT 1确保只给一个结果

场景测试小提示 #

• 场景1：把user_request里的req_start改成'8:00:00'，req_end改成'8:30:00'，运行后会返回Room02的8:00-8:30
• 场景2：改成'5:00:00'到'5:30:00'，会返回任意一个空闲会议室的该时段（如果想指定返回某个类型的，可以在available_rooms里加WHERE r.type = 1这类条件）
• 场景3：改成'9:00:00'到'9:30:00'，此时available_rooms是空的，会自动返回Room01的9:30-10:00

另外，如果那条Room02的10:00-9:00是跨天预订（比如当天10点到次日9点），你可以修改valid_bookings和冲突判断的逻辑，比如：

-- 修改valid_bookings，保留跨天的记录
SELECT * FROM rooms
-- 冲突判断时处理跨天情况
AND (
    -- 正常时段冲突
    (ur.req_start < b.endtime AND ur.req_end > b.starttime)
    -- 跨天时段：用户请求的时段覆盖午夜，和跨天预订冲突
    OR (b.endtime < b.starttime AND (ur.req_start > b.starttime OR ur.req_end < b.endtime))
)

这个就看你实际的业务需求啦～

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