SQL Server：如何处理查询中被0除的情况

Question

我有一个如下所示的查询，它正在获取百分比：

SELECT SUM(convert(decimal(3,2),
          CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1 ELSE 0 END))/
          SUM(convert(decimal(3,2),CASE WHEN State_ID = 14 THEN 1 ELSE 0 END)) * 100
FROM SomeTable

在正常情况下，这可能会返回类似 59.77803 的内容。但事实证明，在某些情况下，第二个 SUM（分母）可能为 0。有谁知道一种方法来解释这种情况并避免除以 0 异常？

我正在使用 SQL Server 2005。谢谢。

Answer 1

我可能会使用 NULL 来表示不可计算，但这取决于您在问题域中的意图（代码扩展以显示发生的情况）：

SELECT SUM(convert(decimal(3,2),
                   CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1 ELSE 0 END)
          )
       /
       NULLIF(
           SUM(convert(decimal(3,2),
                       CASE WHEN State_ID = 14 THEN 1 ELSE 0 END)
           )
           , 0
       )
       * 100
FROM SomeTable

表中没有行时的结果，因此分母为零 - 转换为NULL 因此整个表达式最终为 NULL。

该代码也可以稍微简化（可能是因为它已经是一个简化的玩具问题）：

SELECT SUM( convert(decimal(3,2), CASE WHEN Status_ID = 1 THEN 1 ELSE 0 END) )
       /
       NULLIF( convert(decimal(3,2), COUNT(*)), 0 )
       * 100
FROM SomeTable
WHERE State_ID = 14

作为将许多不同的事物组合到一个查询中的示例：

SELECT CASE WHEN State_ID = 14 THEN 'State_ID = 14'
            WHEN State_ID IN (1, 2, 3) THEN 'State_ID IN (1, 2, 3)'
            ELSE 'DEFAULT'
       END AS Category
       ,SUM(convert(decimal(3,2),
                   CASE WHEN Status_ID = 1 THEN 1 ELSE 0 END)
          )
       /
       convert(decimal(3,2), COUNT(*))
       * 100
FROM SomeTable
GROUP BY CASE WHEN State_ID = 14 THEN 'State_ID = 14'
            WHEN State_ID IN (1, 2, 3) THEN 'State_ID IN (1, 2, 3)'
            ELSE 'DEFAULT'
       END

Answer 2

问问自己，当分母为 0 时，您想要返回什么结果？
然后据此相应地修复查询。

没有好的内置方法可以做到这一点。这是一个商业逻辑的事情。

Answer 3

如果我没读错的话，这应该有效：

SELECT SUM(convert(decimal(3,2), 
          CASE WHEN Status_ID = 1 THEN 1 ELSE 0 END))/ 
          SUM(convert(decimal(3,2),1)) * 100 
FROM SomeTable 
WHERE State_ID = 14 

Answer 4

SELECT SUM(convert(decimal(3,2),
          CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1 ELSE 0 END))/
          SUM(convert(decimal(3,2),CASE WHEN State_ID = 14 THEN 1 ELSE 0 END)) + 0.00000000001 * 100
FROM SomeTable

Answer 5

为了避免除以 0 异常，您可以使用接受分子、分母和默认值的函数，当分母为 0 时传递。并调用该函数进行除法。

CREATE FUNCTION [dbo].[fn_divide] 
(
    @numerator DECIMAL(3,2),@denominator DECIMAL(3,2),@default DECIMAL(3,2)
)  
RETURNS DECIMAL(3,2) 

AS  BEGIN

 DECLARE @result DECIMAL(3,2)

 IF @denominator = 0
 BEGIN
    set @result=@default
 END
ELSE
 BEGIN
    set @result=@numerator/@denominator
 END 

  return @result
END

Answer 6

真正的问题是，当您尝试除以零时，“答案”应该是什么？我会将其设置为 NULL，因此请尝试这样的操作：

DECLARE @x int

set @x=5
select 1.0/NULLIF(@x,0)

set @x=0
select 1.0/NULLIF(@x,0)

输出：

---------------------------------------
0.200000000000

(1 row(s) affected)


---------------------------------------
NULL

(1 row(s) affected)

您的代码将是：

SELECT SUM(convert(decimal(3,2), CASE
                                     WHEN Status_ID = 1 AND State_ID = 14 THEN 1 
                                     ELSE 0 
                                 END
                  )
          ) 
        /
        SUM(convert(decimal(3,2),CASE
                                     WHEN State_ID = 14 THEN 1 
                                     ELSE NULL   --<<<<<<<<<<<<force null if div by zero
                                 END
                   )
           )
        * 100
FROM SomeTable

我不太明白 WHEN Status_ID = 1 AND St​​ate_ID = 14 THEN 1，但那是OP代码。