SQL Server百分比计算

Question

我正在想出一个统一的方式来计算单个表中两列之间的百分比差异。 有时num1为零，在这种情况下，计算的差值应为100％。 有时num2为零，在这种情况下，计算的差异应该是100％。 有时num1和num2之间的差异非常大，在这种情况下，计算出的差异将为100％。

下面是从表中的样品提取液：

declare @numtable table (num1 decimal(10,3) , num2 decimal(10,3)) 
insert into @numtable values (160 , 161.5) 
insert into @numtable values (439 , 377) 
insert into @numtable values (100 , 1) 
insert into @numtable values (1 , 100) 
insert into @numtable values (0 , 20) 
insert into @numtable values (20 , 0) 

我做了一个破SELECT语句如下所示。

select num1 , num2 , Abs(100- (100 * cast(cast(num1 as decimal(6,3))/cast(num2 as decimal(10,3)) as decimal(6,3)))) as percentdiff 
from @numtable 

这使得这个：

num1   num2   percentdiff 
------------ ------------ ------------- 
160.000  161.500  0.900 
439.000  377.000  16.400 
100.000  1.000  9900.000 
1.000  100.000  99.000 
0.000  20.000  100.000 

第3行是OK。 第五行是OK。 第六行从不显示，因为它会生成除零错误。

我希望我的结果集看起来像这样：

num1   num2   percentdiff 
------------ ------------ ------------- 
160.000  161.500  0.900 
439.000  377.000  16.400 
100.000  1.000  9900.000 
1.000  100.000  9900.000 
0.000  20.000  100.000 
20.000  20.000  100.000 

我应该如何改变我的选择到的东西，返回我需要的方式？

谢谢。

Answer 1

我会在您的选择列表的第三项中使用case语句来测试除以0的错误。在其他人做你的数学。就这么简单。

Select num1, num2, 
Case num2 
WHEN 0 Then 100 
Else Abs(100- (100 * cast(cast(num1 as decimal(6,3))/cast(num2 as decimal(10,3)) as decimal(6,3)))) 
END as percentdiff 

From @numtable 

Answer 2

请参阅here。

你只需要通过零情况下做一些在鸿沟额外：

case when num1 <= 0 or num2 <= 0 or num1 = num2 then 100

select num1,num2, 
case when num1 <= 0 or num2 <= 0 or num1 = num2 then 100 
    else 
    case when 
    cast(
    cast(abs(num1-num2) as decimal(8,5)) 
    /
    cast(case when num1>num2 then num2 else num1 end as decimal(8,5)) 
    * 100 
    as decimal(8,2)) 
    > 100 then 
    9900 
    else 
    cast(
    cast(abs(num1-num2) as decimal(8,5)) 
    /
    cast(case when num1>num2 then num2 else num1 end as decimal(8,5)) 
    * 100 
    as decimal(8,2)) 
    end 
    end as percentage 
from t