酒店预订表转入住情况表的SQL查询实现需求

没问题，我来帮你搞定这个需求！你需要把包含入住日、退房日和客人数的预订表，转换成按日期统计当日在店总客人数的表，这个需求在酒店数据分析里很常见，下面我结合你的示例数据给出不同数据库的实现方案：

核心思路 #

我们需要两步来实现：

• 第一步：生成所有需要统计的日期范围（从预订记录里最早的入住日到最晚的退房日）
• 第二步：把这个日期序列和预订表关联，统计每个日期内所有满足「客人在当日在店」的客人数总和（按照你的示例逻辑，客人从入住日到退房日当天都算在店）

假设你的预订表名为 reservations，下面是不同数据库的具体SQL：

MySQL 8.0+ 版本实现

MySQL 8.0及以上支持递归CTE，可以直接生成日期序列：

WITH date_range AS (
    SELECT MIN(arrival_date) AS date
    FROM reservations
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE date < (SELECT MAX(departure_date) FROM reservations)
)
SELECT 
    dr.date,
    SUM(r.guest_number) AS total_guest_number
FROM date_range dr
LEFT JOIN reservations r 
    ON dr.date >= r.arrival_date 
    AND dr.date <= r.departure_date
GROUP BY dr.date
ORDER BY dr.date;

PostgreSQL 实现

PostgreSQL可以用自带的generate_series函数快速生成日期序列，写法更简洁：

SELECT 
    dr.date,
    SUM(r.guest_number) AS total_guest_number
FROM generate_series(
    (SELECT MIN(arrival_date) FROM reservations),
    (SELECT MAX(departure_date) FROM reservations),
    '1 day'::interval
) dr(date)
LEFT JOIN reservations r 
    ON dr.date >= r.arrival_date 
    AND dr.date <= r.departure_date
GROUP BY dr.date
ORDER BY dr.date;

SQL Server 实现

SQL Server同样用递归CTE生成日期序列，如果统计的日期范围超过100天，需要加上OPTION (MAXRECURSION 0)来解除递归次数限制：

WITH date_range AS (
    SELECT MIN(arrival_date) AS date
    FROM reservations
    UNION ALL
    SELECT DATEADD(DAY, 1, date)
    FROM date_range
    WHERE date < (SELECT MAX(departure_date) FROM reservations)
)
SELECT 
    dr.date,
    SUM(r.guest_number) AS total_guest_number
FROM date_range dr
LEFT JOIN reservations r 
    ON dr.date >= r.arrival_date 
    AND dr.date <= r.departure_date
GROUP BY dr.date
ORDER BY dr.date
OPTION (MAXRECURSION 0);

测试验证 #

用你提供的示例预订数据测试上述查询，会得到完全符合预期的结果：

date | total_guest_number
2022-01-01 | 2
2022-01-02 | 5
2022-01-03 | 3

如果你的数据库版本比较旧，不支持CTE或者generate_series，也可以通过创建一个包含连续数字的辅助表来生成日期序列，有需要的话可以再问我~

备注：内容来源于stack exchange，提问作者Ali Majidi