如何对COALESCE返回的含NULL值的字段执行求和运算

问题根因 #

SQL 中任意算术运算只要存在 NULL 操作数，最终结果就为 NULL。你当前的逻辑里，两张表的item_c都为NULL，导致NULLIF(COALESCE(t1.item_c, t2.item_c),0)返回NULL，直接拖垮了整个加法表达式的结果。

解决方案 #

方案1：单字段兜底转0（适用字段少的场景）

在每个字段的计算逻辑外层再加一层COALESCE，把空值转为0后再相加即可，修改后的查询语句如下：

SELECT 
  COALESCE(NULLIF(COALESCE(t1.item_a, t2.item_a), 0), 0) +
  COALESCE(NULLIF(COALESCE(t1.item_b, t2.item_b), 0), 0) +
  COALESCE(NULLIF(COALESCE(t1.item_c, t2.item_c), 0), 0) AS item_sum,
  COALESCE(t1.item_a, t2.item_a) AS item_a,
  COALESCE(t1.item_b, t2.item_b) AS item_b,
  COALESCE(t1.item_c, t2.item_c) AS item_c
FROM table1 t1, table2 t2

方案2：聚合求和（适用字段多的场景）

如果需要求和的字段数量多，嵌套写法维护成本高，可以把字段拆成行后用SUM聚合计算，SUM函数本身会自动忽略NULL值，不需要额外处理空值：

SELECT 
  SUM(NULLIF(COALESCE(t1.item_val, t2.item_val), 0)) AS item_sum,
  MAX(CASE WHEN item_key = 'item_a' THEN COALESCE(t1.item_val, t2.item_val) END) AS item_a,
  MAX(CASE WHEN item_key = 'item_b' THEN COALESCE(t1.item_val, t2.item_val) END) AS item_b,
  MAX(CASE WHEN item_key = 'item_c' THEN COALESCE(t1.item_val, t2.item_val) END) AS item_c
FROM (
  SELECT 'item_a' AS item_key, item_a AS item_val FROM table1
  UNION ALL SELECT 'item_b', item_b FROM table1
  UNION ALL SELECT 'item_c', item_c FROM table1
) t1
JOIN (
  SELECT 'item_a' AS item_key, item_a AS item_val FROM table2
  UNION ALL SELECT 'item_b', item_b FROM table2
  UNION ALL SELECT 'item_c', item_c FROM table2
) t2 ON t1.item_key = t2.item_key

两种方案都可以得到预期的item_sum = 5的结果。

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