oracle SQL查询添加包含空值的两列的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9895141/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query to add values of two columns containing null values?
提问by antonpug
Given table:
给定表：
ID ONE TWO
X1 15 15
X2 10 -
X3 - 20
This query:
这个查询：
SELECT (ONE + TWO) FROM (TABLE)
Just returns the sum of X1's values but not the others since at least one column has a null value. How can I still add them even if there is a null? i.e. consider the null as a 0 maybe?
只返回X1的值的总和，而不是其他值的总和，因为至少一列具有空值。即使有空值，我如何仍然添加它们？即可能将 null 视为 0？
回答by Mosty Mostacho
SELECT (COALESCE(ONE, 0) + COALESCE(TWO, 0)) FROM (TABLE)
COALESCEwill return the first non-null value found in the parameters from left to right. So, when the first field is null, it will take the 0.
COALESCE将返回从左到右在参数中找到的第一个非空值。因此，当第一个字段为空时，它将取 0。
That way, X2will result in 10 + 0 = 10
那样的话，X2会导致10 + 0 = 10
回答by Diego
there is already a good answer, but I think it is worth mention to the antonpug (in case he doesn't know) that the reason why this is happening is that NULL is not a value that can be compared or summed.
已经有一个很好的答案，但我认为值得一提 antonpug（以防他不知道），发生这种情况的原因是 NULL 不是可以比较或求和的值。
NULL isn't 0 or '' (empty string), so every operation involving NULL will result NULL (10 + NULL = NULL), even (NULL=NULL) will evaluate to FALSE
NULL 不是 0 或 ''（空字符串），所以每次涉及 NULL 的操作都会导致 NULL (10 + NULL = NULL)，即使 (NULL=NULL) 也会评估为 FALSE
