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

小开

最佳答案

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

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

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

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

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

小开

使用where子句过滤数据，这样就不会得到0值。

小开

我写了一个函数来处理我的存储过程:

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

小开

没有神奇的全局设置“将除0异常关闭”。该操作必须抛出，因为x/0的数学含义与NULL含义不同，所以它不能返回NULL。我假设您正在处理显而易见的问题，并且您的查询具有消除0除数记录的条件，并且永远不会计算除法。通常的“陷阱”是大多数开发人员期望SQL表现得像过程性语言并提供逻辑运算符短路，但它确实不。我建议你读这篇文章:http://www.sqlmag.com/Articles/ArticleID/9148/pg/2/2.html

小开

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

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

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

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

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

小开

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

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

小开

添加一个CHECK约束，强制Divisor为非零
向表单添加验证器，以便用户不能在该字段中输入零值。

小开

在这种情况下，你可以除以0。商业规则是计算库存周转，你用一段时间内的销售成本，按年计算。在你得到年化数字之后，你要除以该时期的平均库存。

我正在计算在三个月内发生的库存周转次数。我计算了一下，在$1,000的三个月期间，我有Cost of Goods sold。年销售额为$4,000($1,000/3)*12。开始库存是0。期末库存为0。平均库存现在是0。我每年有4000美元的销售额，而且没有库存。这就产生了无数的旋转。这意味着我的所有库存都被客户转换和购买。

这是关于如何计算库存周转的业务规则。

小开

你也可以在查询的开头这样做:

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

因此，如果你有类似100/0的东西，它将返回NULL。我只做了简单的查询，所以我不知道它会如何影响更长的/复杂的查询。

小开

如果你想返回零，如果发生零分割，你可以使用:

SELECT COALESCE(dividend / NULLIF(divisor,0), 0) FROM sometable

对于每一个为0的除数，结果集中都会得到一个0。

小开

当错误传播回调用程序时，可以适当地处理它(如果需要，可以忽略它)。在c#中，SQL中发生的任何错误都会抛出一个异常，我可以捕获并在我的代码中处理，就像任何其他错误一样。

我同意Beska的观点，你不想隐藏错误。你可能不是在处理核反应堆，但隐藏错误通常是一种糟糕的编程实践。这是大多数现代编程语言实现结构化异常处理以将实际返回值与错误/状态代码解耦的原因之一。当你在做数学的时候尤其如此。最大的问题是您无法区分返回的正确计算的0和错误结果的0。相反，返回的任何值都是计算值，如果出现任何错误，则抛出异常。当然，这将根据您访问数据库的方式和使用的语言而有所不同，但您应该总是能够获得可以处理的错误消息。

try
{
Database.ComputePercentage();
}
catch (SqlException e)
{
// now you can handle the exception or at least log that the exception was thrown if you choose not to handle it
// Exception Details: System.Data.SqlClient.SqlException: Divide by zero error encountered.
}

小开

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

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

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

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

将查询重写为:

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

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

小开

对于更新sql:

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

小开

你至少可以阻止查询被错误打断并返回NULL，如果有被0除:

SELECT a / NULLIF(b, 0) FROM t

然而，我会从来没有用coalesce将其转换为零，就像它在其他得到很多赞的答案中所显示的那样。从数学意义上讲，这是完全错误的，甚至是危险的，因为您的应用程序可能会返回错误和误导性的结果。

小开

CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real)
RETURNS Real AS
/*
Purpose:      Handle Division by Zero errors
Description:  User Defined Scalar Function
Parameter(s): @Numerator and @Denominator


Test it:


SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results
UNION ALL
SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0)
UNION ALL
SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16)
UNION ALL
SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL)
UNION ALL
SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL)
UNION ALL
SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0)
UNION ALL
SELECT '16 / 4', dbo.fn_CORP_Divide(16,4)
UNION ALL
SELECT '16 / 3', dbo.fn_CORP_Divide(16,3)


*/
BEGIN
RETURN
CASE WHEN @Denominator = 0 THEN
NULL
ELSE
@Numerator / @Denominator
END
END
GO

小开

使用NULLIF(exp,0)，但以这种方式- NULLIF(ISNULL(exp,0),0)

如果exp为null，则NULLIF(exp,0)中断，但NULLIF(ISNULL(exp,0),0)不会中断

小开

用零代替“除零”是有争议的，但这也不是唯一的选择。在某些情况下，用1代替是(合理的)合适的。我经常发现自己吸毒

 ISNULL(Numerator/NULLIF(Divisor,0),1)

当我在看分数/计数的变化，并希望默认为1，如果我没有数据。例如

NewScore = OldScore *  ISNULL(NewSampleScore/NULLIF(OldSampleScore,0),1)

通常情况下，我实际上在其他地方计算过这个比率(尤其是因为它可以为低分母提供一些非常大的调整因素)。在这种情况下，我通常控制OldSampleScore大于阈值;这就排除了零的可能性。但有时“hack”是合适的。

小开

有时，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

小开

我是这样解决的:

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;

想法吗?