如何避免 SQL 中的“除以零”错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/861778/
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
How to avoid the "divide by zero" error in SQL?
提问by Henrik Staun Poulsen
I have this error message:
我有这个错误信息：
Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.
消息 8134，级别 16，状态 1，第 1 行 遇到除以零错误。
What is the best way to write SQL code so that I will never see this error message again?
编写 SQL 代码以便我再也不会看到此错误消息的最佳方法是什么？
I could do either of the following:
我可以执行以下任一操作：
- Add a where clause so that my divisor is never zero
- 添加 where 子句，以便我的除数永远不会为零
Or
或者
- I could add a case statement, so that there is a special treatment for zero.
- 我可以添加一个 case 语句，以便对零进行特殊处理。
Is the best way to use a NULLIFclause?
是使用NULLIF子句的最佳方式吗？
Is there better way, or how can this be enforced?
有没有更好的方法，或者如何执行？
回答by Henrik Staun Poulsen
In order to avoid a "Division by zero" error we have programmed it like this:
为了避免“被零除”错误，我们对其进行了如下编程：
Select Case when divisor=0 then null
Else dividend / divisor
End ,,,
But here is a much nicer way of doing it:
但这里有一个更好的方法：
Select dividend / NULLIF(divisor, 0) ...
Now the only problem is to remember the NullIf bit, if I use the "/" key.
现在唯一的问题是记住 NullIf 位，如果我使用“/”键。
回答by Tobias Domhan
In case you want to return zero, in case a zero devision would happen, you can use:
如果您想返回零，以防发生零偏差，您可以使用：
SELECT COALESCE(dividend / NULLIF(divisor,0), 0) FROM sometable
For every divisor that is zero, you will get a zero in the result set.
对于每个为零的除数，您将在结果集中得到一个零。
回答by frank
This seemed to be the best fix for my situation when trying to address dividing by zero, which does happen in my data.
在尝试解决除零问题时，这似乎是解决我的情况的最佳方法，这确实发生在我的数据中。
Suppose you want to calculate the male–female ratios for various school clubs, but you discover that the following query fails and issues a divide-by-zero error when it tries to calculate ratio for the Lord of the Rings Club, which has no women:
假设您想计算各个学校俱乐部的男女比例，但您发现以下查询失败并在尝试计算没有女性的指环王俱乐部的比例时出现除以零错误：
SELECT club_id, males, females, males/females AS ratio
FROM school_clubs;
You can use the function NULLIFto avoid division by zero. NULLIFcompares two expressions and returns null if they are equal or the first expression otherwise.
您可以使用该函数NULLIF来避免被零除。NULLIF比较两个表达式，如果它们相等则返回 null，否则返回第一个表达式。
Rewrite the query as:
将查询重写为：
SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio
FROM school_clubs;
Any number divided by NULLgives NULL, and no error is generated.
任何数字除以NULL给出NULL，并且不会产生错误。
回答by Taz
You can also do this at the beginning of the query:
您也可以在查询开始时执行此操作：
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
So if you have something like 100/0it will return NULL. I've only done this for simple queries, so I don't know how it will affect longer/complex ones.
因此，如果您有类似的东西100/0，它将返回 NULL。我只为简单的查询做过这个，所以我不知道它会如何影响更长/复杂的查询。
回答by SQL Police
You can at least stop the query from breaking with an error and return NULLif there is a division by zero:
您至少可以阻止查询因错误而中断，NULL如果除以零则返回：
SELECT a / NULLIF(b, 0) FROM t
However, I would NEVERconvert this to Zero with coalescelike it is shown in that other answer which got many upvotes. This is completely wrong in a mathematical sense, and it is even dangerous as your application will likely return wrong and misleading results.
但是，我永远不会将其转换为零，coalesce就像其他答案中显示的那样，得到了很多赞成。这在数学意义上是完全错误的，甚至是危险的，因为您的应用程序可能会返回错误和误导性的结果。
回答by Beska
EDIT: I'm getting a lot of downvotes on this recently...so I thought I'd just add a note that this answer was written before the question underwent it's most recent edit, where returning null was highlighted as an option...which seems very acceptable. Some of my answer was addressed to concerns like that of Edwardo, in the comments, who seemed to be advocating returning a 0. This is the case I was railing against.
编辑：我最近收到了很多反对票......所以我想我只是添加一个注释，说明这个答案是在问题进行最近的编辑之前写的，其中返回 null 被突出显示为一个选项.. .这似乎非常可以接受。我的一些回答是针对 Edwardo 之类的问题，在评论中，他似乎主张返回 0。这就是我所反对的情况。
ANSWER: I think there's an underlying issue here, which is that division by 0 is not legal. It's an indication that something is fundementally wrong. If you're dividing by zero, you're trying to do something that doesn't make sense mathematically, so no numeric answer you can get will be valid. (Use of null in this case is reasonable, as it is not a value that will be used in later mathematical calculations).
回答：我认为这里有一个潜在的问题，即除以 0 是不合法的。这表明某些事情在根本上是错误的。如果你除以零，你就是在尝试做一些数学上没有意义的事情，所以你能得到的数字答案是无效的。（在这种情况下使用 null 是合理的，因为它不是将在以后的数学计算中使用的值）。
So Edwardo asks in the comments "what if the user puts in a 0?", and he advocates that it should be okay to get a 0 in return. If the user puts zero in the amount, and you want 0 returned when they do that, then you should put in code at the business rules level to catch that value and return 0...not have some special case where division by 0 = 0.
所以爱德华多在评论中问“如果用户输入 0 怎么办？”，他主张得到 0 作为回报应该没问题。如果用户在金额中输入零，并且您希望在他们这样做时返回 0，那么您应该在业务规则级别输入代码以捕获该值并返回 0...没有一些特殊情况，即除以 0 = 0.
That's a subtle difference, but it's important...because the next time someone calls your function and expects it to do the right thing, and it does something funky that isn't mathematically correct, but just handles the particular edge case it's got a good chance of biting someone later. You're not really dividing by 0...you're just returning an bad answer to a bad question.
这是一个微妙的区别，但很重要……因为下次有人调用您的函数并期望它做正确的事情时，它会做一些数学上不正确的时髦事情，但只是处理特定的边缘情况，它有一个以后咬人的好机会。你并没有真正除以 0……你只是对一个糟糕的问题给出了一个糟糕的答案。
Imagine I'm coding something, and I screw it up. I should be reading in a radiation measurement scaling value, but in a strange edge case I didn't anticipate, I read in 0. I then drop my value into your function...you return me a 0! Hurray, no radiation! Except it's really there and it's just that I was passing in a bad value...but I have no idea. I want division to throw the error because it's the flag that something is wrong.
想象一下，我正在编码一些东西，然后我把它搞砸了。我应该读入辐射测量缩放值，但在我没有预料到的奇怪边缘情况下，我读入了 0。然后我将我的值放入您的函数中……您返回给我 0！万岁，没有辐射！除了它真的在那里，只是我传递了一个糟糕的价值......但我不知道。我希望除法抛出错误，因为这是错误的标志。
回答by Beska
SELECT Dividend / ISNULL(NULLIF(Divisor,0), 1) AS Result from table
By catching the zero with a nullif(), then the resulting null with an isnull() you can circumvent your divide by zero error.
通过使用 nullif() 捕获零，然后使用 isnull() 产生的空值可以避免除以零错误。
回答by N Mason
Replacing "divide by zero" with zero is controversial - but it's also not the only option. In some cases replacing with 1 is (reasonably) appropriate. I often find myself using
用零代替“除以零”是有争议的——但这也不是唯一的选择。在某些情况下，用 1 替换是（合理地）合适的。我经常发现自己使用
ISNULL(Numerator/NULLIF(Divisor,0),1)
when I'm looking at shifts in scores/counts, and want to default to 1 if I don't have data. For example
当我查看分数/计数的变化时，如果我没有数据，希望默认为 1。例如
NewScore = OldScore * ISNULL(NewSampleScore/NULLIF(OldSampleScore,0),1)
More often than not, I've actually calculated this ratio somewhere else (not least because it can throw some very large adjustment factors for low denominators. In this case I'd normally control for OldSampleScore is greater than a threshold; which then precludes zero. But sometimes the 'hack' is appropriate.
通常情况下，我实际上已经在其他地方计算了这个比率（尤其是因为它可以为低分母抛出一些非常大的调整因子。在这种情况下，我通常会控制 OldSampleScore 大于阈值；然后排除零. 但有时'hack' 是合适的。
回答by Ron Savage
I wrote a function a while back to handle it for my stored procedures:
不久前我写了一个函数来为我的存储过程处理它：
print 'Creating safeDivide Stored Proc ...'
go
if exists (select * from dbo.sysobjects where name = 'safeDivide') drop function safeDivide;
go
create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19))
returns decimal(38,19)
begin
-- **************************************************************************
-- Procedure: safeDivide()
-- Author: Ron Savage, Central, ex: 1282
-- Date: 06/22/2004
--
-- Description:
-- This function divides the first argument by the second argument after
-- checking for NULL or 0 divisors to avoid "divide by zero" errors.
-- Change History:
--
-- Date Init. Description
-- 05/14/2009 RS Updated to handle really freaking big numbers, just in
-- case. :-)
-- 05/14/2009 RS Updated to handle negative divisors.
-- **************************************************************************
declare @p_product decimal(38,19);
select @p_product = null;
if ( @divisor is not null and @divisor <> 0 and @Numerator is not null )
select @p_product = @Numerator / @divisor;
return(@p_product)
end
go
回答by finnw
- Add a CHECK constraint that forces
Divisorto be non-zero - Add a validator to the form so that the user cannot enter zero values into this field.
- 添加强制
Divisor为非零的 CHECK 约束 - 向表单添加验证器，以便用户无法在此字段中输入零值。
