SQL Server 检查 IsNull 和零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/580364/
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 Server Check for IsNull and for Zero
提问by Jeff
I have the following:
我有以下几点：
set @SomeVariable = @AnotherVariable/isnull(@VariableEqualToZero,1) - 1
If @VariableEqualToZero is null it substitutes the 1. I need it to substitute 1 if @VariableEqualToZero = 0 as well. How do I do this?
如果@VariableEqualToZero 为空，它会替换 1。如果 @VariableEqualToZero = 0，我也需要它替换 1。我该怎么做呢？
回答by Orion Leung
If you're using SQL Server, you can probably use a NULLIFstatement?
i.e. set the value to NULLif it's 0then set it to 1if it's NULL- should catch for both 0's and NULLs:
如果您使用的是 SQL Server，您可能可以使用NULLIF语句？
ie 将值设置为NULLif it's0然后将它设置为1if it's NULL- 应该同时捕获 0 和 NULL：
SET @SomeVariable = @AnotherVariable/ISNULL(NULLIF(@VariableEqualToZero,0),1) - 1
回答by user53794
SET @SomeVariable = @AnotherVariable / COALESCE(
CASE
WHEN @VariableEqualToZero = 0 THEN 1
ELSE @VariableEqualToZero
END, 1) - 1
回答by Eric Rosenberger
set @SomeVariable = @AnotherVariable /
(case when isnull(@VariableEqualToZero, 0) = 0 then 1 else
@VariableEqualToZero end) - 1
回答by Will Rickards
You use CASE
您使用 CASE
instead of
代替
ISNULL(@VariableEqualToZero,1)
use
用
CASE WHEN @VariableEqualToZero IS NULL OR @VariableEqualToZero = 0 THEN 1 ELSE @VariableEqualToZero END
COALESCE and ISNULL are essentially just shortcuts for a CASE statement. You can consult the help for the syntax of CASE.
COALESCE 和 ISNULL 本质上只是 CASE 语句的快捷方式。您可以参考 CASE 的语法帮助。
