mysql - 从 COALESCE 中减去

Question

我有一个看起来像这样的 SQL 查询:

SELECT 
COALESCE(field1, 0) AS field1, 
COALESCE(field2, 0) AS field2,
COALESCE(field3 - field1 - field2, 0) AS field
FROM ...

However the problem with it is that given say:

field1 = 1
field2 = 0
field3 = 2

当显示输出时，field3 显示 0，尽管 2 - 1 - 0 = 1。

使用 COALESCE 是因为我在外部加入了一些可能导致 Null 值的表。

如果 field3 为 null，我也会遇到任何问题吗？

谢谢

Answer 1

独立进行合并:

SELECT COALESCE(field1, 0) AS field1, COALESCE(field2, 0) AS field2,
       COALESCE(field3, 0) - coalesce(field1, 0) - coalesce(field2, 0) AS field
FROM ...

对于您的示例，我怀疑 field2 实际上是 NULL 而不是 0。