SELECT Dividend / ISNULL(NULLIF(Divisor,0), 1) AS Result from table

通过使用nullif()捕获零，然后使用isnull()捕获结果为零，可以避免除零错误。

对于更新sql:

update Table1 set Col1 = Col2 / ISNULL(NULLIF(Col3,0),1)

为了避免“除零”错误，我们编写了这样的程序:

Select Case when divisor=0 then null
Else dividend / divisor
End ,,,

但这里有一种更好的方式:

Select dividend / NULLIF(divisor, 0) ...

现在唯一的问题是记住NullIf位，如果我使用“/”键。

There is no magic global setting 'turn division by 0 exceptions off'. The operation has to to throw, since the mathematical meaning of x/0 is different from the NULL meaning, so it cannot return NULL. I assume you are taking care of the obvious and your queries have conditions that should eliminate the records with the 0 divisor and never evaluate the division. The usual 'gotcha' is than most developers expect SQL to behave like procedural languages and offer logical operator short-circuit, but it does NOT. I recommend you read this article: http://www.sqlmag.com/Articles/ArticleID/9148/pg/2/2.html

这似乎是解决我的情况的最佳方法，当我试图解决除零的问题时，这种情况确实发生在我的数据中。

假设您想要计算各个学校俱乐部的男女比例，但是您发现下面的查询失败了，并且在试图计算指环王俱乐部的比例时出现了一个除零的错误，因为指环王俱乐部没有女性:

SELECT club_id, males, females, males/females AS ratio
  FROM school_clubs;

可以使用NULLIF函数来避免除零。NULLIF比较两个表达式，如果相等则返回null，否则返回第一个表达式。

将查询重写为:

SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio
  FROM school_clubs;

任何数字除以NULL都会得到NULL，并且不会产生错误。

我是这样解决的:

IIF(ValueA != 0, Total / ValueA, 0)

它可以被包装在一个更新中:

SET Pct = IIF(ValueA != 0, Total / ValueA, 0)

或者在选择中:

SELECT IIF(ValueA != 0, Total / ValueA, 0) AS Pct FROM Tablename;

想法吗?