如何在MySQL中创建查询以统计指定日期区间内每日的在住客人数量

看起来你需要的是统计指定时间段内每一天的在住客人总数，而不是按预订的入住日期来统计——这确实是住宿类系统里很常见的客情统计需求。我来帮你拆解解决思路，直接给你可用的SQL，再把每一步的作用讲清楚。

核心思路 #

要实现这个需求，我们得先拿到统计区间内的所有连续日期，然后对每一天，找出所有在当天处于入住状态的预订，最后把这些预订对应的客人数量累加起来。

解决方案（MySQL 8.0+ 推荐） #

MySQL 8.0及以上支持递归CTE（公共表表达式），用它生成日期序列最简洁。下面的SQL你可以直接用，只需要修改开头的起始和结束日期就行：

WITH RECURSIVE date_range AS (
    -- 设置统计的起始日期
    SELECT '2024-11-01' AS date
    UNION ALL
    -- 递归生成后续每一天，直到到达结束日期
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE date < '2024-12-31' -- 设置统计的结束日期
)
SELECT
    dr.date,
    COUNT(rc.id_guest) AS total_guests
FROM date_range dr
-- 筛选出当天处于入住状态的预订：入住<=当天，退房>当天（退房当天默认不算在住）
LEFT JOIN reservations r
    ON r.checkin <= dr.date AND r.checkout > dr.date
-- 关联客人关联表，获取每个在住预订的所有客人记录
LEFT JOIN rel_reservations_guests rc
    ON rc.id_res = r.id_res
-- 按日期分组统计
GROUP BY dr.date
ORDER BY dr.date ASC;

老版本MySQL兼容方案（低于8.0） #

如果你的MySQL版本不支持递归CTE，可以用数字表生成日期序列，效果完全一致：

SELECT
    dr.date,
    COUNT(rc.id_guest) AS total_guests
FROM (
    -- 生成0-365的数字序列，用来拼接出日期
    SELECT 
        DATE_ADD('2024-11-01', INTERVAL n DAY) AS date
    FROM (
        SELECT a.N + b.N * 10 + c.N * 100 AS n
        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) a,
            (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) b,
            (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) c
    ) numbers
    -- 过滤出我们需要的日期区间
    WHERE DATE_ADD('2024-11-01', INTERVAL n DAY) <= '2024-12-31'
) dr
LEFT JOIN reservations r
    ON r.checkin <= dr.date AND r.checkout > dr.date
LEFT JOIN rel_reservations_guests rc
    ON rc.id_res = r.id_res
GROUP BY dr.date
ORDER BY dr.date ASC;

关键细节调整 #

1. 在住规则修改：如果你的业务逻辑是退房当天客人仍算在住，把预订筛选条件里的r.checkout > dr.date改成r.checkout >= dr.date即可。
2. 唯一客人统计：如果你需要统计去重后的唯一客人（比如关联表里重复的客人只算1次），把COUNT(rc.id_guest)替换成COUNT(DISTINCT rc.id_guest)就行。

匹配你的示例数据 #

用你提供的测试数据跑这个SQL，会完全符合你的预期：

• 2024-11-08的total_guests为1（仅Joe在住）
• 2024-12-17的total_guests为8（id_res=2的5条客人记录 + id_res=3的3条客人记录）

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