postgresql - 排除任何带有空值的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9673370/
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
postgresql - exclude any result with nulls
提问by canisrufus
I'm doing a bunch of sum queries: SELECT col1 + col2 + col3 + ...
我正在做一堆求和查询： SELECT col1 + col2 + col3 + ...
Some of the values in some of the columns are null. I'm checking for them by doing
某些列中的某些值为空。我正在检查它们
SELECT CASE WHEN col1 is not null and col2 is not null and ...
I'm wondering if there is a more concise syntax for accomplishing this task.
我想知道是否有更简洁的语法来完成这项任务。
回答by
Well, since the sum of any number and nullis null, you can do something like the following (with the obvious ...filled in):
好吧，由于任何数字和nullis的总和null，您可以执行以下操作（...填写明显的内容）：
select big_honking_sum
from(
select col1+col2+col3+...+coln as big_honking_sum
from some_table
)sum
where big_honking_sum is not null;
回答by John P
Use COALESCE function if you can accept that a NULL value will be treated as 0.
如果您可以接受 NULL 值将被视为 0，请使用 COALESCE 函数。
SELECT COALESCE(Col1,0)
+ COALESCE(Col2,0)
+ COALESCE(Col3,0)
AS TOTAL FROM table;
Perhaps you could consider using 0 as default value instead of NULL if applicable.
如果适用，也许您可以考虑使用 0 作为默认值而不是 NULL。
回答by Erwin Brandstetter
You can have that even simpler:
你可以更简单：
SELECT foo
FROM ...
WHERE (-1 IN (col1, col2, col3)) IS NOT NULL
The IN expression will return NULLif (and only if) there is at least one NULLvalue among the tested values (and no match). So the whole expression evaluates to TRUE if (and only if) there is no NULL.
NULL当（且仅当）NULL测试值中至少有一个值（且没有匹配项）时，IN 表达式将返回。因此，当（且仅当）没有NULL.
Edit:I have to correct myself! A positive match would stop the evaluation and return TRUE, even if a NULLis among the values. So you need a value that is guaranteed not to be in the set. Like 0 where all values are > 0 or -1 where all values are positive or you cannot use this expression for the purpose.
编辑：我必须纠正自己！正匹配将停止评估并返回TRUE，即使 aNULL在值中。所以你需要一个保证不在集合中的值。像 0，其中所有值都 > 0 或 -1，其中所有值都是正数，或者您不能为此目的使用此表达式。
