MySQL如何从日期字段按月提取数据实现billing表行转列统计

你写的SQL返回的是长表结构，每行对应单个站点单个年份单个月的金额，不符合1-12月作为独立列的宽表要求，也缺失了无数据月份补0的逻辑。同时原有SQL中误将表名billing写为bills，也会导致执行报错。

MySQL实现这类行转列的宽表需求，直接使用条件聚合即可，兼容同站点同月份有多条账单的场景，自动对无数据月份补0：

SELECT 
    site_name,
    YEAR(billing_date) AS YearName,
    SUM(CASE WHEN MONTH(billing_date) = 1 THEN amount ELSE 0 END) AS `1月`,
    SUM(CASE WHEN MONTH(billing_date) = 2 THEN amount ELSE 0 END) AS `2月`,
    SUM(CASE WHEN MONTH(billing_date) = 3 THEN amount ELSE 0 END) AS `3月`,
    SUM(CASE WHEN MONTH(billing_date) = 4 THEN amount ELSE 0 END) AS `4月`,
    SUM(CASE WHEN MONTH(billing_date) = 5 THEN amount ELSE 0 END) AS `5月`,
    SUM(CASE WHEN MONTH(billing_date) = 6 THEN amount ELSE 0 END) AS `6月`,
    SUM(CASE WHEN MONTH(billing_date) = 7 THEN amount ELSE 0 END) AS `7月`,
    SUM(CASE WHEN MONTH(billing_date) = 8 THEN amount ELSE 0 END) AS `8月`,
    SUM(CASE WHEN MONTH(billing_date) = 9 THEN amount ELSE 0 END) AS `9月`,
    SUM(CASE WHEN MONTH(billing_date) = 10 THEN amount ELSE 0 END) AS `10月`,
    SUM(CASE WHEN MONTH(billing_date) = 11 THEN amount ELSE 0 END) AS `11月`,
    SUM(CASE WHEN MONTH(billing_date) = 12 THEN amount ELSE 0 END) AS `12月`
FROM billing
GROUP BY site_name, YEAR(billing_date)
ORDER BY site_name, YearName;

针对你提供的示例数据，执行后的输出结果如下：

如果你需要用英文月份名作为列名，将MONTH(billing_date)的判断替换为monthname(billing_date)匹配对应月份名即可。

内容的提问来源于stack exchange，提问作者A.A Noman