Oracle SQL 语法 - 检查多个列是否为 IS NOT NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8596004/
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
Oracle SQL Syntax - Check multiple columns for IS NOT NULL
提问by jbreed
Is there a more concise syntax for checking whether multiple columns are not null? I'm looking to simplify
是否有更简洁的语法来检查多列是否不为空？我想简化
weight IS NOT NULL AND
weight_unit IS NOT NULL AND
length IS NOT NULL AND
width IS NOT NULL AND
height IS NOT NULL AND
dimensional_unit IS NOT NULL
into something shorter.
变成更短的东西。
Using Oracle, if it's relevant.
使用 Oracle（如果相关）。
回答by dani herrera
With De Morgan's law:
根据德摩根定律：
NOT (A OR B) = (NOT A) AND (NOT B)
NOT (A OR B) = (NOT A) AND (NOT B)
you save 20 chars ;)
你节省了 20 个字符；)
NOT (
weight IS NULL OR
weight_unit IS NULL OR
length IS NULL OR
width IS NULL OR
height IS NULL OR
dimensional_unit IS NULL
)
回答by Florin Ghita
As far as I know, there isn't such a syntax.
据我所知，没有这样的语法。
But if all of them are numeric you can use this trick:
但是如果它们都是数字，你可以使用这个技巧：
weight + weight_unit + length + width + height + dimensional_unit is not null
回答by Sigurrd Greinert
ALTER TABLE X
ADD CONSTRAINT C_X_NN
CHECK ( (
DECODE (weight, NULL, 0, 1) +
DECODE (weight_unit, NULL, 0, 1) +
DECODE (length , NULL, 0, 1) +
DECODE (width , NULL, 0, 1) +
DECODE (height , NULL, 0, 1) +
DECODE (dimensional_unit , NULL, 0, 1)
) = 0
);
回答by Amol
I tried to solve this problem, If all columns/variables are of type numeric or varchar2 then we can use following way in sql, pl/sql :
我试图解决这个问题，如果所有列/变量都是 numeric 或 varchar2 类型，那么我们可以在 sql, pl/sql 中使用以下方式：
LENGTH(COL1||COL2||COL3) IS NOT NULL
Hope this will help you.
希望这会帮助你。
回答by Matt Donnan
If you want to check if ALL the columns are NOT NULL then you could Concatenate your columns first and then test once for IS NOT NULL e.g:
如果你想检查所有的列是否都是 NOT NULL 那么你可以先连接你的列，然后测试一次 IS NOT NULL 例如：
weight || weight_unit || length || width || height || dimensional_unit IS NOT NULL
This will check if all the values together across those fields do not come to a total of NULL.
这将检查这些字段中的所有值是否总和为 NULL。
If just testing whether there may be a NULL among them then I think your original statement from the question is still the best way to do it.
如果只是测试其中是否可能存在 NULL ，那么我认为您对问题的原始陈述仍然是最好的方法。
回答by Rabi Panda
How about this ..... coalesce(field1,field2,....,fieldn) is not null
这个怎么样.....coalesce(field1,field2,....,fieldn) 不为空
I think this would serve the purpose!!!!
我认为这将达到目的！！！！
