【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