构建SQL查询统计指定时段内每日有效预约数（含跨期预约）

这个需求我太熟了！跨天预约的每日统计确实容易卡壳，核心是得先把目标时间段的所有日期列出来，再逐个判断每个预约是否覆盖当天，最后统计数量。下面分不同主流数据库给你具体的实现方案：

1. 生成指定区间（2018年4月）的所有日期列表，确保每一天都被包含
2. 将日期列表和预约表关联，判断预约时间是否覆盖当天：只要预约的开始时间不晚于当天结束，且结束时间不早于当天开始，就说明该预约需要计入当天的统计
3. 按日期分组，统计每日的预约数量

PostgreSQL 版本 #

PostgreSQL自带的generate_series函数可以直接生成日期序列，非常方便：

WITH date_range AS (
    SELECT generate_series(
        '2018-04-01'::date,
        '2018-04-30'::date,
        '1 day'::interval
    ) AS stat_date
)
SELECT
    dr.stat_date,
    COUNT(r.id) AS reservation_count
FROM date_range dr
LEFT JOIN reservation r ON
    r.start <= dr.stat_date + INTERVAL '1 day'  -- 预约开始不晚于当天23:59:59
    AND r.end >= dr.stat_date  -- 预约结束不早于当天00:00:00
GROUP BY dr.stat_date
ORDER BY dr.stat_date;

MySQL 版本 #

MySQL 8.0+（支持递归CTE） #

用递归CTE生成日期序列：

WITH RECURSIVE date_range AS (
    SELECT '2018-04-01' AS stat_date
    UNION ALL
    SELECT DATE_ADD(stat_date, INTERVAL 1 DAY)
    FROM date_range
    WHERE stat_date < '2018-04-30'
)
SELECT
    dr.stat_date,
    COUNT(r.id) AS reservation_count
FROM date_range dr
LEFT JOIN reservation r ON
    r.start <= DATE_ADD(dr.stat_date, INTERVAL 1 DAY)
    AND r.end >= dr.stat_date
GROUP BY dr.stat_date
ORDER BY dr.stat_date;

MySQL 5.x（无递归CTE） #

需要先创建一个数字辅助表（如果没有的话），用来生成日期：

-- 先创建数字表（只需要创建一次）
CREATE TABLE numbers (n INT);
INSERT INTO numbers VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),
(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30);

-- 统计查询
SELECT
    DATE_ADD('2018-04-01', INTERVAL n DAY) AS stat_date,
    COUNT(r.id) AS reservation_count
FROM numbers
LEFT JOIN reservation r ON
    r.start <= DATE_ADD(DATE_ADD('2018-04-01', INTERVAL n DAY), INTERVAL 1 DAY)
    AND r.end >= DATE_ADD('2018-04-01', INTERVAL n DAY)
WHERE DATE_ADD('2018-04-01', INTERVAL n DAY) <= '2018-04-30'
GROUP BY stat_date
ORDER BY stat_date;

SQL Server 版本 #

用递归CTE生成日期序列，注意设置递归次数限制：

WITH date_range AS (
    SELECT CAST('2018-04-01' AS DATE) AS stat_date
    UNION ALL
    SELECT DATEADD(DAY, 1, stat_date)
    FROM date_range
    WHERE stat_date < CAST('2018-04-30' AS DATE)
)
SELECT
    dr.stat_date,
    COUNT(r.id) AS reservation_count
FROM date_range dr
LEFT JOIN reservation r ON
    r.start <= DATEADD(DAY, 1, dr.stat_date)
    AND r.end >= dr.stat_date
GROUP BY dr.stat_date
ORDER BY dr.stat_date
OPTION (MAXRECURSION 31); -- 一个月最多31天，设置足够的递归次数

• 确保start和end字段是datetime/timestamp类型，避免类型转换导致的逻辑错误
• 如果你的业务中，end字段是闭区间（比如预约到4月2日00:00:00意味着包含4月2日零点），可能需要把关联条件调整为r.end > dr.stat_date，避免把已结束的预约重复统计
• 使用LEFT JOIN可以保证即使当天没有预约，也会显示reservation_count = 0，不会遗漏日期

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