MySQL查询：按月统计不同状态学生数量（近16个月）

需求目标 #

统计近16个月内每月各状态的学生数量，需满足：

• 无数据的月份返回0值
• 按student_bursary_status的不同值分列展示
• 统计范围覆盖status_start至status_end的整个周期
• 若学生同月内拥有多个状态，对应状态列分别计数，但total列仅统计该学生1次

初始查询语句及问题 #

初始查询代码：

select * from (
SELECT year(status_start) as year,MONTH(status_start) AS MONTH,
count( `students`.id) as total
FROM `students` 
INNER JOIN `student_bursaries` ON `students`.`id` = `student_bursaries`.`student_id` 
INNER JOIN `student_bursary_statuses` ON  `student_bursaries`.`id`=`student_bursary_statuses`.`student_bursary_id`
GROUP BY YEAR(student_bursary_statuses.status_start),MONTH(student_bursary_statuses.status_start)
order by year desc, month desc limit 16
) tmp order by year, month

存在问题：

1. 无数据的月份未返回0值
2. 仅统计学生总数，未按状态值分列展示
3. 仅基于status_start日期统计，未覆盖status_start至status_end的完整周期

第一次更新后的查询及剩余问题 #

更新后查询代码：

select * from (
SELECT year(status_start) as year,MONTH(status_start) AS MONTH,
count( `students`.id) as total,
  COUNT( IF( status = 1, 1, NULL ) ) as status_1,
  COUNT( IF( status = 2, 1, NULL ) ) as status_2,
  COUNT( IF( status = 3, 1, NULL ) ) as status_3,
  COUNT( IF( status = 4, 1, NULL ) ) as status_4
FROM `students` 
INNER JOIN `student_bursaries` ON `students`.`id` =     `student_bursaries`.`student_id` 
INNER JOIN `student_bursary_statuses` ON  `student_bursaries`.`id`=`student_bursary_statuses`.`student_bursary_id`
GROUP BY YEAR(student_bursary_statuses.status_start),MONTH(student_bursary_statuses.status_start)
order by year desc, month desc limit 16
) tmp order by year, month

剩余问题：

1. 依旧仅基于status_start统计，未覆盖status_start至status_end的周期
2. 需手动枚举所有状态类型，无法自动根据数据库中实际存在的status值生成对应列

第二次更新后的查询及待优化点 #

更新后查询已接近目标，但日历表的日期分组存在问题，需调整为仅按年、月分组：

select 
dateTable.date,
COALESCE(yourQuery.total,0) AS mtotal,
COALESCE(yourQuery.status_1,0) AS s1,
COALESCE(yourQuery.status_2,0) AS s2,
COALESCE(yourQuery.status_3,0) AS s3,
COALESCE(yourQuery.status_4,0) AS s4
FROM
(
  SELECT (CURDATE() - INTERVAL c.number MONTH) AS date
  FROM (SELECT singles + tens + hundreds number FROM 
  ( SELECT 0 singles
  UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
  UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
  UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
  ) singles JOIN 
  (SELECT 0 tens
  UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
  UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
  UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
  ) tens  JOIN 
  (SELECT 0 hundreds
  UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
  UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
  UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
  ) hundreds
  ORDER BY number DESC) c  
  WHERE c.number BETWEEN 0 and 16
) as datetable
LEFT JOIN
(
 SELECT 
  status_start,status_end,
  year(status_start) as year,MONTH(status_start) AS MONTH,
  count(`students`.id) as total,
  COUNT( IF( status = 1, 1, NULL ) ) as status_1,
  COUNT( IF( status = 2, 1, NULL ) ) as status_2,
  COUNT( IF( status = 3, 1, NULL ) ) as status_3,
  COUNT( IF( status = 4, 1, NULL ) ) as status_4
  FROM `students` 
  INNER JOIN `student_bursaries` ON `students`.`id` =     `student_bursaries`.`student_id` 
  INNER JOIN `student_bursary_statuses` ON  `student_bursaries`.`id`=`student_bursary_statuses`.`student_bursary_id`
  GROUP BY YEAR(student_bursary_statuses.status_start),MONTH(student_bursary_statuses.status_start)
) AS yourQuery
ON dateTable.date between yourQuery.status_start and yourQuery.status_end
ORDER BY dateTable.date

待优化点：调整日历表逻辑，仅按年、月维度分组，而非具体日期

预期输出示例 #

|year   |MONTH  |total |1|2|3|4
|2021   |6      |0     |0|0|0|0    
|2021   |7      |0     |0|0|0|0
|2021   |8      |0     |0|0|0|0
|2021   |9      |0     |0|0|0|0
|2021   |10     |0     |0|0|0|0
|2021   |11     |0     |0|0|0|0
|2021   |12     |0     |0|0|0|0
|2022   |1      |2     |2|0|0|0
|2022   |2      |2     |2|0|0|0
|2022   |3      |2     |2|0|0|0
|2022   |4      |2     |2|0|0|0
|2022   |5      |3     |3|0|0|0
|2022   |6      |3     |3|1|0|0
|2022   |7      |3     |2|0|1|0
|2022   |8      |3     |2|0|0|1
|2022   |9      |3     |2|1|1|1

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