H2数据库中ISNULL/IFNULL使用及月度平均油量计算问题

开发者阿泽

2026-5-21

看起来你遇到的问题是——那些从未有过订单的卡车，因为左连接后orderDate是NULL，所以monthname(orderDate)也返回NULL，导致这些卡车只会在结果里出现一行（对应month为NULL的分组），而不是每个月份都有记录对吧？

要解决这个问题，核心是先构建一个包含所有月份的基准数据集，然后把它和卡车表做交叉连接，这样就能得到「所有卡车+所有月份」的完整组合，再左连接订单数据计算平均值，就能保证每个卡车每个月都有对应的行，没订单的月份平均油量自然就是0了。

下面是适配H2数据库的具体查询语句：

WITH months AS (
    -- 生成1-12月的数字列表，用来匹配订单的月份
    SELECT 1 AS month_num 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
    t.truckCode,
    -- 根据月份数字生成对应的英文月份名称（用2000年的日期只是为了获取名称，不影响结果）
    MONTHNAME(DATE '2000-' || m.month_num || '-01') AS month,
    -- 依然用IFNULL把null的平均值替换为0
    IFNULL(AVG(o.petrolQty), 0) AS avgPetrolQty
FROM truck t
-- 交叉连接月份表，得到所有卡车和所有月份的组合
CROSS JOIN months m
-- 左连接订单表，同时匹配卡车ID和月份
LEFT JOIN orderz o
    ON t.truckId = o.truckId
    AND MONTH(o.orderDate) = m.month_num
    -- 如果需要统计特定年份的月度数据，取消下面这行注释并修改年份
    -- AND YEAR(o.orderDate) = 2024
-- 按卡车编码和月份数字分组（用数字分组/排序比月份名称更可靠，避免字母排序混乱）
GROUP BY t.truckCode, m.month_num, MONTHNAME(DATE '2000-' || m.month_num || '-01')
-- 按卡车编码和月份数字排序，保证月份顺序正确
ORDER BY t.truckCode, m.month_num;

我给你拆解下关键逻辑：

months CTE生成了1到12的月份数字，这是我们的基准月份列表，确保每个月都能被覆盖到。
CROSS JOIN会把每辆卡车和每个月份都组合一遍，哪怕卡车没有任何订单，每个月也会有对应的空行等待填充数据。
左连接订单时加上MONTH(o.orderDate) = m.month_num，只会把对应月份的订单数据关联到当前行，不会把其他月份的订单混进来干扰平均值计算。
用month_num来分组和排序，比直接用月份名称更稳妥——毕竟按字母排序的话，"April"会在"August"前面，但数字排序能保证1月到12月的自然顺序。

如果你的需求是统计特定年份的月度平均，记得打开AND YEAR(o.orderDate) = 2024这行注释，把年份改成你需要的就行。

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