【SQL】根据年份，查询每个月的数据量,漫步者r1600t08（sql按照指定年月查询）

文件名：【SQL】根据年份，查询每个月的数据量,漫步者r1600t08 【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