MySQL子查询本地正常服务器异常 无LAG/LEAD如何查询空余房间

子查询本地正常、服务器执行失败的常见原因 #

• MySQL版本差异：本地MySQL版本高于服务器版本，用到了高版本才支持的语法特性，是最常见的诱因。
• 权限配置差异：服务器上的数据库账号没有子查询涉及的表/视图的查询权限，或者子查询涉及的临时表、文件操作相关权限未开启。
• 数据环境差异：服务器端的表结构、索引配置、数据量、字符集排序规则和本地不一致，比如本地表存在某字段但服务器端已删除，或字符集不兼容导致条件匹配失效。
• 运行配置差异：服务器端的MySQL配置参数（比如max_allowed_packet、sql_mode）和本地不同，比如本地关闭了ONLY_FULL_GROUP_BY而服务器开启，会导致非聚合字段未出现在GROUP BY中的子查询报错。
• 部署错误：部署过程中脚本字符编码转换出现乱码，或复制粘贴时丢失/增加了特殊字符，导致子查询语法解析失败。

无窗口函数实现指定日期区间空余房间查询方案 #

本方案兼容MySQL 5.5及以上所有版本，Linux环境可直接运行，单天空余房间也可被正常识别。

前置表结构假设 #

示例基于以下表结构实现，可根据实际业务调整字段名、筛选条件：

• 房间表room：核心字段room_id（房间ID）
• 预订表room_booking：核心字段booking_id（订单ID）、room_id（关联房间ID）、checkin_date（入住日期）、checkout_date（退房日期）、status（订单状态，已生效预订状态值为1）

步骤1：生成指定区间的连续日期序列 #

不需要额外建表，通过UNION生成数字序列即可拼接出查询时间段的所有日期，示例为查询2024-05-01到2024-05-10的日期，跨度更大时可扩展UNION的数字项：

SELECT 
    DATE_ADD('2024-05-01', INTERVAL t.n DAY) AS stat_date
FROM (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    UNION ALL SELECT 8 UNION ALL SELECT 9
) t
WHERE DATE_ADD('2024-05-01', INTERVAL t.n DAY) <= '2024-05-10'

步骤2：关联匹配空余房间 #

核心逻辑为：只要某房间在某一天没有被任何生效预订覆盖，就判定为当日空余，单天空余也会生成独立记录：

-- 替换为实际的查询起始、结束日期
SET @start_date = '2024-05-01';
SET @end_date = '2024-05-10';

SELECT DISTINCT r.room_id, d.stat_date AS vacant_date
FROM room r
-- 关联连续日期序列，生成所有房间+所有查询日期的笛卡尔积
CROSS JOIN (
    SELECT DATE_ADD(@start_date, INTERVAL t.n DAY) AS stat_date
    FROM (
        SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
        UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
        UNION ALL SELECT 8 UNION ALL SELECT 9 -- 日期跨度超过10天时按需扩展更多数字
    ) t
    WHERE DATE_ADD(@start_date, INTERVAL t.n DAY) <= @end_date
) d
-- 左关联预订表，判断日期是否被预订覆盖
LEFT JOIN room_booking b 
ON r.room_id = b.room_id 
AND b.status = 1
AND d.stat_date >= b.checkin_date 
AND d.stat_date < b.checkout_date
-- 未匹配到预订记录的即为当日空余
WHERE b.booking_id IS NULL
ORDER BY r.room_id, d.stat_date;

可选：聚合为连续空余时间段 #

如果需要合并连续的空余日期为时间段展示，可基于上述查询结果做二次聚合：

SELECT 
    room_id,
    MIN(vacant_date) AS vacant_start,
    MAX(vacant_date) AS vacant_end,
    DATEDIFF(MAX(vacant_date), MIN(vacant_date)) + 1 AS vacant_days
FROM (
    SELECT DISTINCT r.room_id, d.stat_date AS vacant_date
    FROM room r
    CROSS JOIN (
        SELECT DATE_ADD(@start_date, INTERVAL t.n DAY) AS stat_date
        FROM (
            SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
            UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
            UNION ALL SELECT 8 UNION ALL SELECT 9
        ) t
        WHERE DATE_ADD(@start_date, INTERVAL t.n DAY) <= @end_date
    ) d
    LEFT JOIN room_booking b 
    ON r.room_id = b.room_id 
    AND b.status = 1
    AND d.stat_date >= b.checkin_date 
    AND d.stat_date < b.checkout_date
    WHERE b.booking_id IS NULL
) t
GROUP BY room_id, DATE_SUB(vacant_date, INTERVAL 
    (SELECT COUNT(*) 
     FROM (
         SELECT DISTINCT r1.room_id, d1.stat_date AS vd
         FROM room r1
         CROSS JOIN (
             SELECT DATE_ADD(@start_date, INTERVAL t1.n DAY) AS stat_date
             FROM (
                 SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
                 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
                 UNION ALL SELECT 8 UNION ALL SELECT 9
             ) t1
             WHERE DATE_ADD(@start_date, INTERVAL t1.n DAY) <= @end_date
         ) d1
         LEFT JOIN room_booking b1 
         ON r1.room_id = b1.room_id 
         AND b1.status = 1
         AND d1.stat_date >= b1.checkin_date 
         AND d1.stat_date < b1.checkout_date
         WHERE b1.booking_id IS NULL
     ) t2 
     WHERE t2.room_id = t.room_id AND t2.vd < t.vacant_date
    ) DAY
)
ORDER BY room_id, vacant_start;

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