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

为了避免“除以零”错误，我们将其编程如下：

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

但这里有一个更好的方法：

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

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

如果您想返回零，以防发生零偏差，您可以使用：

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

对于每个为零的除数，您将在结果集中得到一个零。

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

假设您要计算各个学校俱乐部的男女比例，但您发现以下查询失败并在尝试计算没有女性的魔戒俱乐部的比例时出现除零错误：

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

您也可以在查询开头执行此操作：

SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF

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

如果除以零，您至少可以阻止查询中断并返回 NULL：

SELECT a / NULLIF(b, 0) FROM t 

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

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

通过用 nullif() 捕捉零，然后用 isnull() 捕捉到的 null，您可以避免除以零错误。

编辑：我最近对此有很多反对意见......所以我想我只是添加一个注释，这个答案是在问题经历它最近的编辑之前写的，其中返回 null 被突出显示为一个选项.. .这似乎很可接受。在评论中，我的一些回答是针对像 Edwardo 这样的担忧，他似乎主张返回 0。这就是我所反对的情况。

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

因此，Edwardo 在评论中询问“如果用户输入 0 会怎样？”，他主张应该可以得到 0 作为回报。如果用户在金额中输入零，并且您希望在他们这样做时返回 0，那么您应该在业务规则级别输入代码以捕获该值并返回 0...没有一些除以 0 = 的特殊情况0。

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

想象一下，我正在编写一些东西，但我把它搞砸了。我应该读取辐射测量缩放值，但在一个我没有预料到的奇怪边缘情况下，我读取了 0。然后我将我的值放入你的函数中......你给我一个 0！万岁，无辐射！除了它真的在那里，只是我传递了一个错误的价值......但我不知道。我希望除法抛出错误，因为它是出现问题的标志。

用零代替“除以零”是有争议的——但这也不是唯一的选择。在某些情况下，用 1 代替是（合理地）合适的。我经常发现自己在使用

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

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

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

通常情况下，我实际上已经在其他地方计算了这个比率（尤其是因为它可以为低分母抛出一些非常大的调整因子。在这种情况下，我通常会控制 OldSampleScore 大于阈值；然后排除零.但有时'hack'是合适的。

不久前我写了一个函数来为我的 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

添加强制除数为非零的 CHECK 约束 向表单添加验证器，以便用户无法在此字段中输入零值。

对于更新 SQL：

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

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

这是您可以除以零的情况。业务规则是，要计算库存周转率，您需要计算一段时间内的销售成本，然后将其年化。获得年化数字后，除以该期间的平均库存。

我正在研究计算三个月内发生的库存周转次数。我已经计算出我在三个月内销售的商品成本为 1,000 美元。年销售额为 4,000 美元（1,000 美元/3）*12。期初库存为 0。期末库存为 0。我现在的平均库存为 0。我的年销售额为 4000 美元，没有库存。这会产生无限数量的转弯。这意味着我所有的库存都被客户转换和购买。

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

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

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

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

想法？

当错误传播回调用程序时，您可以适当地处理错误（或者如果这是您想要的，则忽略它）。在 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.
}

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

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