如何高效查询当月预订量少于5的日期

核心思路 #

把原来的30次单日期查询合并成一次批量查询：先生成当月完整的日期范围，再聚合统计每天的预订量，最后关联两者筛选出预订量不足5的日期，彻底解决多次查询的性能问题。

具体实现（MySQL 8.0+ 版本） #

用递归CTE生成当月所有日期，再关联预订统计结果筛选目标日期：

WITH RECURSIVE dates AS (
    -- 生成当月第一天
    SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01') AS date
    UNION ALL
    -- 递归生成后续日期，直到当月最后一天
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM dates
    WHERE date < LAST_DAY(CURDATE())
)
SELECT d.date
FROM dates d
-- 左连接当月每天的预订统计结果
LEFT JOIN (
    SELECT bookingDate, COUNT(*) AS booking_count
    FROM your_table_name
    WHERE bookingDate BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND LAST_DAY(CURDATE())
    GROUP BY bookingDate
) b ON d.date = b.bookingDate
-- 无预订的日期按0计算，筛选出数量少于5的日期
WHERE COALESCE(b.booking_count, 0) < 5
ORDER BY d.date;

性能优化补充 #

给bookingDate字段添加索引，能大幅加快聚合统计的速度：

CREATE INDEX idx_booking_date ON your_table_name(bookingDate);

兼容MySQL 5.x 版本（无CTE支持） #

如果你的MySQL版本不支持递归CTE，可以用数字辅助表生成当月日期（假设存在一个numbers表，包含num字段，值从0到31）：

SELECT 
    DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL num DAY) AS date
FROM numbers
WHERE num < DAY(LAST_DAY(CURDATE()))
-- 关联预订统计并筛选
LEFT JOIN (
    SELECT bookingDate, COUNT(*) AS booking_count
    FROM your_table_name
    WHERE bookingDate BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND LAST_DAY(CURDATE())
    GROUP BY bookingDate
) b ON date = b.bookingDate
WHERE COALESCE(b.booking_count, 0) < 5
ORDER BY date;

效果说明 #

这种方案只需要执行1次查询就能获取所有目标日期，相比原来的30次查询性能提升明显；加上索引后，即使数据量较大，统计和关联的速度也能得到保障。

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