根据年份，查询每个月的数据量
- 一种
WITH Months AS (SELECT 1 AS Month 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 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
)
SELECT m.Month, COALESCE(COUNT(yt.createTime), 0) AS NumberOfRecords
FROM Months m
LEFT JOIN student yt ON MONTH(yt.createTime) = m.Month AND YEAR(yt.createTime) = :year
GROUP BY m.Month
ORDER BY m.Month
- 二种
WITH Months AS (SELECT 1 AS Month 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 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
)
SELECT CONCAT(CAST(:year AS CHAR), '-', LPAD(m.Month, 2, '0')) AS YearMonth, COALESCE(COUNT(yt.createTime), 0) AS NumberOfRecords
FROM Months m
LEFT JOIN student yt ON MONTH(yt.createTime) = m.Month AND YEAR(yt.createTime) = :year
GROUP BY YearMonth
ORDER BY YearMonth
