本文介绍如何通过单条 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与MySQL的经典之作，书中不但全面介绍了两种技术的核心特性，还讲解了如何高效地结合这两种技术构建健壮的数据驱动的应用程序。本书涵盖了两种技术新版本中出现的最新特性，书中大量实际的示例和深入的分析均来自于作者在这方面多年的专业经验，可用于解决开发者在实际中所面临的各种挑战。
立即学习“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 模式；
- 日期比较务必避免函数包裹字段：保障索引有效性，提升大数据量下的响应速度。
通过这一模式，你不仅能优雅解决“六周分计”需求，更能将其扩展至任意维度的条件聚合（如按状态、地区、类型等多维交叉统计），是构建高效数据服务的基础技能。
