I've read about nullif, but haven't been able to put together a SELECT statement to use it properly:
我读过关于nullif的文章，但是还没有找到一个SELECT语句来正确地使用它:
Select (num/total) as percent ...
If total is 0, I want to return Null. How do I revise the statement?
如果total是0，我想返回Null。我该如何修改声明?
if I say
如果我说
select (num/nullif(total, 0)) as percent ...
what does it mean to divide by Null?
除以零意味着什么?
2 个解决方案
#1
4
You could use CASE:
你可以用例:
SELECT [percent] = CASE WHEN total = 0 THEN NULL ELSE (num/total) END
FROM Table
#2
0
To answer to the second part of your question:
回答你问题的第二部分:
what does it mean to divide by Null?
除以零意味着什么?
The result of an arithmetic calculation which uses a NULL value is NULL, or to put it another way:
使用空值的算术计算结果为空，或者换一种说法:
Because Null is not a data value, but a marker for an unknown value, using mathematical operators on Null results in an unknown value, which is represented by Null.
因为Null不是一个数据值，而是一个未知值的标记，使用Null上的数学运算符会导致一个未知值，这个未知值由Null表示。
So the statement
因此,声明
select (num/nullif(total, 0)) as percent
will return NULL if total = 0. The use of CASE, as presented by Tim, is an equally valid way of handling the problem.
如果total = 0，则返回NULL。正如Tim所说，用CASE是处理问题的一种同样有效的方法。
#1
4
You could use CASE:
你可以用例:
SELECT [percent] = CASE WHEN total = 0 THEN NULL ELSE (num/total) END
FROM Table
#2
0
To answer to the second part of your question:
回答你问题的第二部分:
what does it mean to divide by Null?
除以零意味着什么?
The result of an arithmetic calculation which uses a NULL value is NULL, or to put it another way:
使用空值的算术计算结果为空，或者换一种说法:
Because Null is not a data value, but a marker for an unknown value, using mathematical operators on Null results in an unknown value, which is represented by Null.
因为Null不是一个数据值，而是一个未知值的标记，使用Null上的数学运算符会导致一个未知值，这个未知值由Null表示。
So the statement
因此,声明
select (num/nullif(total, 0)) as percent
will return NULL if total = 0. The use of CASE, as presented by Tim, is an equally valid way of handling the problem.
如果total = 0，则返回NULL。正如Tim所说，用CASE是处理问题的一种同样有效的方法。