SQL查询：统计同房间连续预订的Switchovers（换房次数）

已实现的日期统计SQL #

我已经编写了以下SQL查询，用于统计指定$date日期的入住人数（total_arrivals）、退房人数（total_departures）及在住人数（total_stayovers）：

SELECT
    SUM(CASE WHEN CAST(check_in AS date) = CAST('$date' as date) THEN 1 ELSE 0 END) AS total_arrivals,
    SUM(CASE WHEN CAST(check_out AS date) = CAST('$date' as date) THEN 1 ELSE 0 END) AS total_departures,
    SUM(CASE WHEN (CAST(check_in AS date) <= CAST('$date' as date) AND CAST(check_out AS date) > CAST('$date' as date)) THEN 1 ELSE 0 END) AS total_stayovers
FROM bookings_beds24

需求：统计换房（Switchovers） #

现在需要编写SQL查询统计换房（Switchovers）：即同一room_id下，存在两个不同预订，其中一个的check_out日期与另一个的check_in日期相同的情况。

请问仅通过SQL查询能否实现该需求？我目前尝试了如下无效查询：

SELECT * 
FROM 
    (SELECT check_in FROM bookings_beds24 WHERE room_id='208979') AS a, 
    (SELECT COUNT(book_id) AS total FROM bookings_beds24 WHERE check_out=a.check_in AND room_id='208979') AS b;

解答：完全可以用SQL实现换房统计 #

核心思路是将表与自身做自关联，匹配同一房间下，一个预订的退房日期等于另一个预订的入住日期，同时排除同一个预订的重复匹配。

方案1：列出所有换房记录对 #

如果需要查看具体的换房对应记录，可以用这个查询：

SELECT
    b1.room_id,
    b1.book_id AS 前序预订ID,
    CAST(b1.check_out AS DATE) AS 换房日期,
    b2.book_id AS 后续预订ID,
    CAST(b2.check_in AS DATE) AS 后续入住日期
FROM bookings_beds24 b1
JOIN bookings_beds24 b2
    ON b1.room_id = b2.room_id
    AND CAST(b1.check_out AS DATE) = CAST(b2.check_in AS DATE)
    AND b1.book_id != b2.book_id -- 避免同一个预订自匹配
ORDER BY b1.room_id, 换房日期;

方案2：按房间+日期统计换房次数 #

如果需要统计每日每个房间的换房次数，用分组查询：

SELECT
    room_id,
    CAST(b1.check_out AS DATE) AS 换房日期,
    COUNT(*) AS 当日换房次数
FROM bookings_beds24 b1
JOIN bookings_beds24 b2
    ON b1.room_id = b2.room_id
    AND CAST(b1.check_out AS DATE) = CAST(b2.check_in AS DATE)
    AND b1.book_id != b2.book_id
GROUP BY room_id, CAST(b1.check_out AS DATE)
ORDER BY room_id, 换房日期;

原查询无效的原因 #

你之前的查询用了交叉关联，但子查询b中直接引用外部表a的字段属于错误用法，这类关联逻辑必须用JOIN来明确关联条件，否则会触发语法错误或逻辑异常。

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