如何在MySQL中统计每月指定状态的预订记录数（适配PHP导出）

我之前做过类似的图表数据统计需求，刚好能给你一套高效的解决方案，分MySQL查询和PHP处理两部分来说：

一、核心MySQL查询语句 #

要直接得到mm/yy -> 条目数的格式，同时保证查询效率，用下面的SQL就可以：

SELECT
  DATE_FORMAT(BookingDate, '%m/%y') AS month_year, -- 直接格式化为mm/yy格式
  COUNT(*) AS record_count
FROM
  your_booking_table -- 替换成你的表名
WHERE
  Status IN ('Closed', 'Open', 'Confirmed') -- 用IN比多个OR更高效
GROUP BY
  month_year -- 按格式化后的月份分组统计
ORDER BY
  STR_TO_DATE(month_year, '%m/%y') ASC; -- 转成日期排序，避免字符串排序的混乱（比如01/23不会排在12/22后面）

关键细节说明： #

• DATE_FORMAT：直接在数据库层面完成日期格式化，减少PHP端的处理工作量，也避免不同环境下的日期格式差异。
• IN子句：对比Status = 'Closed' OR Status = 'Open' OR Status = 'Confirmed'，IN的可读性和执行效率都更优。
• 排序逻辑：如果直接按month_year字符串排序，会出现10/22排在09/23前面的问题，用STR_TO_DATE转成日期类型再排序，能保证时间顺序正确。

二、性能优化建议 #

如果你的数据量较大，建议给BookingDate和Status创建联合索引，让WHERE过滤和GROUP BY分组都能命中索引，大幅提升查询速度：

CREATE INDEX idx_booking_date_status ON your_booking_table(BookingDate, Status);

三、PHP端转成图表可用数组 #

拿到MySQL的查询结果后，用PHP把它转成适合图表的数组非常简单，这里用PDO示例（mysqli写法类似）：

// 假设你已经通过PDO建立了数据库连接，$pdo是连接实例
$sql = "SELECT DATE_FORMAT(BookingDate, '%m/%y') AS month_year, COUNT(*) AS record_count FROM your_booking_table WHERE Status IN ('Closed', 'Open', 'Confirmed') GROUP BY month_year ORDER BY STR_TO_DATE(month_year, '%m/%y') ASC";

$stmt = $pdo->prepare($sql);
$stmt->execute();

// 转成键为月份、值为数量的关联数组，直接适配大部分图表库（比如ECharts、Chart.js）
$chartData = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $chartData[$row['month_year']] = (int)$row['record_count'];
}

// 如果需要给前端传递数据，直接转成JSON即可
// echo json_encode($chartData);

四、可选扩展：显示无数据的月份（避免图表断层） #

如果你的图表需要展示所有连续月份（哪怕某个月没有符合条件的记录也要显示0），可以用MySQL 8.0+支持的递归CTE生成完整的月份序列，再左连接统计结果：

WITH RECURSIVE date_range AS (
  -- 取数据库中最早的预订月份作为起始点
  SELECT DATE_FORMAT(MIN(BookingDate), '%Y-%m-01') AS month_start
  FROM your_booking_table
  UNION ALL
  -- 递归生成后续每个月的第一天
  SELECT DATE_ADD(month_start, INTERVAL 1 MONTH)
  FROM date_range
  WHERE month_start <= DATE_FORMAT(CURDATE(), '%Y-%m-01') -- 截止到当前月份
)
SELECT
  DATE_FORMAT(d.month_start, '%m/%y') AS month_year,
  COALESCE(b.record_count, 0) AS record_count -- 把NULL转成0
FROM
  date_range d
LEFT JOIN (
  -- 子查询按月统计有效记录数
  SELECT
    DATE_FORMAT(BookingDate, '%Y-%m-01') AS month_start,
    COUNT(*) AS record_count
  FROM your_booking_table
  WHERE Status IN ('Closed', 'Open', 'Confirmed')
  GROUP BY month_start
) b ON d.month_start = b.month_start
ORDER BY
  d.month_start ASC;

这样就能保证每个月份都有数据，图表不会出现断层，体验更好。

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