MySQL查询构建：按月份统计各场地预订量适配Google Chart

如果你需要统计全年各场地的预订量，并且要支持按周/月切换统计周期，同时输出的数据格式能直接用于Google Chart折线图，这个MySQL查询方案刚好能满足你的需求：

按月统计的核心查询 #

SELECT 
  DATE_FORMAT(b.booking_date, '%Y-%m') AS booking_month,
  SUM(f.facility_name = 'Pitch 1') AS Pitch1,
  SUM(f.facility_name = 'Pitch 2') AS Pitch2,
  SUM(f.facility_name = 'Training Pitch 1') AS TPitch1,
  SUM(f.facility_name = 'Training Pitch 2') AS TPitch2,
  SUM(f.facility_name = 'Front Bar') AS FrontBar,
  SUM(f.facility_name = 'Back Bar') AS BackBar,
  SUM(f.facility_name = 'Gaelscoil Building 1') AS Gaelscoil1,
  SUM(f.facility_name = 'Gaelscoil Building 2') AS Gaelscoil2
FROM tbl_booking b
INNER JOIN tbl_facility f ON f.facility_id = b.facility_id
GROUP BY DATE_FORMAT(b.booking_date, '%Y-%m')
ORDER BY DATE_FORMAT(b.booking_date, '%Y-%m')

关键细节说明 #

• 切换统计周期：如果需要按周统计，只需要把所有DATE_FORMAT里的%Y-%m替换成周格式即可。常用的周格式有：
  • %Y-%u：按ISO周（周一为一周的第一天）
  • %Y-%v：按周日为一周第一天的周数
    替换后的查询示例我放在下面了。
• 场地预订量统计：这里用了SUM(f.facility_name = '场地名称')的小技巧——在MySQL中，布尔表达式会被转换为1（成立）或0（不成立），所以SUM的结果就是对应场地的总预订次数，写法比CASE WHEN更简洁高效。
• 数据格式适配Google Chart：查询输出的结构是「周期字段 + 各场地预订量列」，刚好匹配Google Chart折线图的数据源要求——你可以直接把booking_month（或booking_week）设为X轴，各个场地列作为不同的折线系列，省去额外的数据转换步骤。

按周统计的查询示例 #

SELECT 
  DATE_FORMAT(b.booking_date, '%Y-%u') AS booking_week,
  SUM(f.facility_name = 'Pitch 1') AS Pitch1,
  SUM(f.facility_name = 'Pitch 2') AS Pitch2,
  SUM(f.facility_name = 'Training Pitch 1') AS TPitch1,
  SUM(f.facility_name = 'Training Pitch 2') AS TPitch2,
  SUM(f.facility_name = 'Front Bar') AS FrontBar,
  SUM(f.facility_name = 'Back Bar') AS BackBar,
  SUM(f.facility_name = 'Gaelscoil Building 1') AS Gaelscoil1,
  SUM(f.facility_name = 'Gaelscoil Building 2') AS Gaelscoil2
FROM tbl_booking b
INNER JOIN tbl_facility f ON f.facility_id = b.facility_id
GROUP BY DATE_FORMAT(b.booking_date, '%Y-%u')
ORDER BY DATE_FORMAT(b.booking_date, '%Y-%u')

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