如何避免SQL中的“除零”错误?

我有这个错误信息:

Msg 8134，级别16，状态1，第1行，除以零错误。

写SQL代码的最好方法是什么，这样我就再也不会看到这个错误消息了?

我可以做以下任何一件事:

添加where子句，使除数永远不为零

我可以添加一个case语句，这样就可以对0进行特殊处理。

使用NULLIF子句是最好的方法吗?

有没有更好的办法，或者如何实施?

当前回答

有时，0可能不合适，但有时1也不合适。有时，从0到100,000,000的跳跃被描述为1%或100%的变化也可能是误导性的。在这种情况下，100,000,000 %可能是合适的。这取决于你打算根据百分比或比率得出什么样的结论。

例如，一个非常小的销售项目从2-4个销量变化，而一个非常大的销售项目从1,000,000个销量变化到2,000,000个销量变化，对分析师或管理层来说可能意味着非常不同的东西，但都是100%或1个变化。

隔离NULL值可能比在一堆混合了合法数据的0%或100%行中搜索要容易得多。通常，分母中的0可能表示错误或缺少值，您可能不想只是为了使数据集看起来整洁而填充任意值。

CASE
     WHEN [Denominator] = 0
     THEN NULL --or any value or sub case
     ELSE [Numerator]/[Denominator]
END as DivisionProblem

其他回答

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

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

对于更新sql:

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

CASE
     WHEN [Denominator] = 0
     THEN NULL --or any value or sub case
     ELSE [Numerator]/[Denominator]
END as DivisionProblem

编辑: 最近我得到了很多反对票…所以我想我只是添加了一个注释，这个答案是在问题经历了最近的编辑之前写的，返回null作为一个选项被高亮显示…这似乎是可以接受的。我的一些回答是针对Edwardo的担忧，在评论中，他似乎主张返回0。这就是我反对的案子。

答: 我认为这里有一个潜在的问题，即除以0是不合法的。这表明某些事情从根本上是错误的。如果你在除以0，你在尝试做一些数学上没有意义的事情，所以你得到的数字答案都是无效的。(在这种情况下使用null是合理的，因为它不是一个将在以后的数学计算中使用的值)。

所以Edwardo在评论中问道:“如果用户输入一个0呢?”他主张得到一个0应该是可以的。如果用户在金额中输入0，并且您希望在他们这样做时返回0，那么您应该在业务规则级别放入代码以捕获该值并返回0…没有一些特殊情况，除以0 = 0。

这是一个微妙的区别，但很重要……因为下次有人调用你的函数并希望它做正确的事情时，它做了一些奇怪的事情，从数学上来说是不正确的，但只是处理特定的边界情况，它有很好的机会在以后咬人。你不是真的除以0…你只是对一个糟糕的问题给出了一个糟糕的答案。

想象一下，我在写代码，结果搞砸了。我应该读取辐射测量缩放值，但在一个我没有预料到的奇怪边缘情况下，我读取的是0。然后我将我的值放入你的函数中…你给我一个0!万岁，没有辐射!除了它真的在那里，只是我传入了一个坏的值…但我不知道。我想让除法抛出错误因为它是出错的标志。

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

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

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，并且不会产生错误。

推荐文章