本文介绍如何通过单条 SQL 语句同时统计同一数据表中多个时间区间的记录数量（如连续六周的预约数），避免多次查询开销，重点解析 CASE WHEN + SUM() 的高性能写法及常见陷阱。
本文介绍如何通过单条 sql 语句同时统计同一数据表中多个时间区间的记录数量（如连续六周的预约数），避免多次查询开销，重点解析 `case when + sum()` 的高性能写法及常见陷阱。
在开发报表或数据看板时，常需对同一张表（如 booking）按不同时间窗口（例如连续六周）分别统计记录数，并按 screen_id 分组聚合。若采用六次独立 SELECT COUNT(*) 查询，不仅增加网络往返与数据库连接压力，还难以在 PHP 中统一关联结果。理想方案是单次查询返回六列独立计数，兼顾性能与可维护性。
✅ 推荐方案：CASE WHEN + SUM()（一行一屏，六列计数）
MySQL 不支持直接在 SELECT 子句中对不同 WHERE 条件执行多个 COUNT(*)，但可利用布尔表达式在算术上下文中的隐式转换（TRUE → 1, FALSE → 0）配合 SUM() 实现等效效果：
SELECT screen_id, screen, screen_code, SUM((start_date <= ? AND DATE(end_date) >= ?)) AS firstweekcount, SUM((start_date <= ? AND DATE(end_date) >= ?)) AS secondweekcount, SUM((start_date <= ? AND DATE(end_date) >= ?)) AS thirdweekcount, SUM((start_date <= ? AND DATE(end_date) >= ?)) AS fourthweekcount, SUM((start_date <= ? AND DATE(end_date) >= ?)) AS fifthweekcount, SUM((start_date <= ? AND DATE(end_date) >= ?)) AS sixthweekcount FROM booking GROUP BY screen_id, screen, screen_code;
? 关键说明：
- 每个 SUM(...) 内部是一个布尔条件表达式，MySQL 将其转为 0/1 后累加，等价于 COUNT(*) 满足该条件的行数；
- ? 占位符应按顺序绑定六对日期参数（$monday_week1, $sunday_week1, …, $monday_week6, $sunday_week6），务必使用预处理语句防止 SQL 注入；
- GROUP BY 必须包含所有非聚合字段（screen_id, screen, screen_code），否则在 ONLY_FULL_GROUP_BY 模式下会报错。
⚠️ 常见错误与修正
你原先尝试的写法逻辑正确，但存在两个隐患：
立即学习“PHP免费学习笔记（深入）”；
未明确 GROUP BY 字段完整性
若 screen 和 screen_code 与 screen_id 并非完全函数依赖（即一个 screen_id 对应多个 screen 值），MySQL 8.0+ 会拒绝执行。应显式列出全部非聚合字段。-
日期函数滥用导致索引失效
DATE(end_date) 会使 end_date 列无法使用索引。更优写法是改用范围比较：-- ❌ 低效（无法走索引） DATE(end_date) >= '2024-06-10' -- ✅ 高效（可命中索引） end_date >= '2024-06-10 00:00:00' AND end_date < '2024-06-11 00:00:00'
因此，建议将 sunday_weekX 转换为当天结束时间（'YYYY-MM-DD 23:59:59'），并重写条件为：
start_date <= ? AND end_date >= ?
? PHP 使用示例（PDO 预处理）
<?php
$pdo = new PDO($dsn, $user, $pass);
$stmt = $pdo->prepare("
SELECT
screen_id, screen, screen_code,
SUM((start_date <= ? AND end_date >= ?)) AS firstweekcount,
SUM((start_date <= ? AND end_date >= ?)) AS secondweekcount,
-- ... 其余四组参数
FROM booking
GROUP BY screen_id, screen, screen_code
");
// 绑定12个参数：6组（周一上限、周日下限）
$params = [
$monday_week1, $sunday_week1_end, // '2024-06-03', '2024-06-09 23:59:59'
$monday_week2, $sunday_week2_end,
// ... 共12个值
];
$stmt->execute($params);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $row) {
echo "Screen {$row['screen_id']}: Week1={$row['firstweekcount']}, Week2={$row['secondweekcount']}\n";
}? 总结
- 优先使用 SUM(CASE WHEN ... THEN 1 ELSE 0 END) 或简写 SUM(condition)：单次扫描完成全部计数，性能最优；
- 避免子查询嵌套（如 SELECT (SELECT ...) FROM DUAL）：虽语法可行，但 MySQL 会为每个子查询单独执行全表扫描，N 倍性能损耗；
- 严格校验 GROUP BY 字段：确保语义清晰且兼容高版本 SQL 模式；
- 日期比较务必避免函数包裹字段：保障索引有效性，提升大数据量下的响应速度。
通过这一模式，你不仅能优雅解决“六周分计”需求，更能将其扩展至任意维度的条件聚合（如按状态、地区、类型等多维交叉统计），是构建高效数据服务的基础技能。
