如何编写MySQL查询实现多年度月度用水量对比报表？

嗨Ruediger，针对你想要生成各年度月度用水量对比视图的需求，这里有两种实用的实现思路，你可以根据自己的数据库环境来选择：

方法一：使用条件聚合（推荐，适合固定年份的场景） #

这种方法通过CASE WHEN语句在聚合时筛选对应年份的数据，直接将行数据转为列，写法简洁高效：

SELECT 
    MONTHNAME(TIMESTAMP) AS Month,
    -- 提取2018年对应月份的用水量，空值用0填充避免显示NULL
    IFNULL(MAX(CASE WHEN YEAR(TIMESTAMP) = 2018 THEN VALUE END), 0) AS '2018',
    -- 提取2019年对应月份的用水量
    IFNULL(MAX(CASE WHEN YEAR(TIMESTAMP) = 2019 THEN VALUE END), 0) AS '2019'
FROM fhem.history
WHERE 
    DEVICE LIKE 'Wasserverbrauch' 
    AND READING LIKE 'statStateMonthLast'
    AND YEAR(TIMESTAMP) IN (2018, 2019) -- 只筛选需要的年份，提升查询效率
GROUP BY MONTH(TIMESTAMP), MONTHNAME(TIMESTAMP) -- 按月份数字+名称分组，避免多语言环境下的名称差异
ORDER BY MONTH(TIMESTAMP); -- 按月份顺序排序，保证结果从1月到12月展示

小提示： #

如果你的数据库是多语言环境，用MONTH(TIMESTAMP)分组比仅用MONTHNAME更可靠——不同语言的月份名称会有差异（比如德语的1月是Januar而非Jan），按月份数字分组能确保同月份的数据被正确聚合。

方法二：使用自连接（适合需要动态扩展年份的场景） #

如果后续可能要添加更多年份，自连接的方式可以通过多次关联来扩展，不过写法会稍繁琐一些：

SELECT 
    a.Month,
    a.`2018`,
    IFNULL(b.`2019`, 0) AS '2019' -- 用0替代NULL，保证数值统一
FROM (
    -- 查询2018年的数据
    SELECT 
        MONTHNAME(TIMESTAMP) AS Month,
        VALUE AS '2018'
    FROM fhem.history
    WHERE 
        DEVICE LIKE 'Wasserverbrauch' 
        AND READING LIKE 'statStateMonthLast'
        AND YEAR(TIMESTAMP) = 2018
) a
LEFT JOIN (
    -- 查询2019年的数据
    SELECT 
        MONTHNAME(TIMESTAMP) AS Month,
        VALUE AS '2019'
    FROM fhem.history
    WHERE 
        DEVICE LIKE 'Wasserverbrauch' 
        AND READING LIKE 'statStateMonthLast'
        AND YEAR(TIMESTAMP) = 2019
) b ON a.Month = b.Month
ORDER BY MONTH(a.Month); -- 按月份顺序排序

补充说明： #

使用LEFT JOIN可以保证即使某一年某个月份没有数据，也会显示该月份（对应年份的数值会被IFNULL处理为0），如果不需要替换NULL，直接写b.2019``即可。

内容的提问来源于stack exchange，提问作者Puetong0815