【SQL】根据年份，查询每个月的数据量

根据年份，查询每个月的数据量

• 一种

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