sql 补全空白月份

以下是SQL补全空白月份的方法：

假设有一个表名为table1，其中包含日期字段date和数据字段data。现在需要补全空白月份的数据，可以使用以下SQL语句：

SELECT 
    DATE_FORMAT(date,'%Y-%m') AS month,
    SUM(data) AS total,
    SUM(CASE DATE_FORMAT(date,'%m') WHEN '01' THEN data ELSE 0 END) AS '一月',
    SUM(CASE DATE_FORMAT(date,'%m') WHEN '02' THEN data ELSE 0 END) AS '二月',
    SUM(CASE DATE_FORMAT(date,'%m') WHEN '03' THEN data ELSE 0 END) AS '三月',
    SUM(CASE DATE_FORMAT(date,'%m') WHEN '04' THEN data ELSE 0 END) AS '四月',
    SUM(CASE DATE_FORMAT(date,'%m') WHEN '05' THEN data ELSE 0 END) AS '五月',
    SUM(CASE DATE_FORMAT(date,'%m') WHEN '06' THEN data ELSE 0 END) AS '六月',
    SUM(CASE DATE_FORMAT(date,'%m') WHEN '07' THEN data ELSE 0 END) AS '七月',
    SUM(CASE DATE_FORMAT(date,'%m') WHEN '08' THEN data ELSE 0 END) AS '八月',
    SUM(CASE DATE_FORMAT(date,'%m') WHEN '09' THEN data ELSE 0 END) AS '九月',
    SUM(CASE DATE_FORMAT(date,'%m') WHEN '10' THEN data ELSE 0 END) AS '十月',
    SUM(CASE DATE_FORMAT(date,'%m') WHEN '11' THEN data ELSE 0 END) AS '十一月',
    SUM(CASE DATE_FORMAT(date,'%m') WHEN '12' THEN data ELSE 0 END) AS '十二月'
FROM (
    SELECT 
        DATE_FORMAT(date_add('2022-01-01', INTERVAL a + b DAY),'%Y-%m-%d') AS date
    FROM 
        (SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t1,
        (SELECT 0 AS b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30) AS t2
) AS t
LEFT JOIN table1 ON DATE_FORMAT(table1.date,'%Y-%m') = t.date
GROUP BY month
ORDER BY month;

该SQL语句使用了左连接（LEFT JOIN）和子查询（Subquery）的方式，将一个包含所有月份的临时表（t）与原始表（table1）进行连接，然后使用SUM和CASE函数对每个月份的数据进行求和，最终得到补全空白月份的结果。