SQL SERVER 中处理分母为0的情况

首先写个测试数据，然后我们来求一下colone除以coltwo的商

declare @table table (id int,colone numeric(4,2),coltwo numeric(2,1))

insert into @table

select 1,1.2,0.4 union all

select 2,1.4,0.6 union all

select 3,1.8,0.8 union all

select 4,9.1,1.2 union all

select 5,9.02,2.9 union all

select 6,9.11,3.4 union all

select 7,8.23,2.3 union all

select 8,12.11,0 union all

select 9,2.3,8.1 union all

select 10,2.5,0

 

我们来看一下数据表中的数据：

select * from @table

/*

id          colone                                  coltwo

----------- ---------------------------------------

1           1.20                                    0.4

2           1.40                                    0.6

3           1.80                                    0.8

4           9.10                                    1.2

5           9.02                                    2.9

6           9.11                                    3.4

7           8.23                                    2.3

8           12.11                                   0.0

9           2.30                                    8.1

10          2.50                                    0.0

*/

 

如果直接这样写会报错：

select colone/coltwo from @table

错误信息：Divide by zero error encountered.

 

怎么来处理这个问题呢？

 

--方法一：case when

select round(colone/case coltwo when 0 then null else coltwo end,2) as result1 from @table

/*

result1

---------------------------------------

3.000000

2.330000

2.250000

7.580000

3.110000

2.680000

3.580000

NULL

*/

--方法二：nullif

select round(colone/nullif(coltwo,0),2) as result2 from @table

/*

result2

---------------------------------------

3.000000

2.330000

2.250000

7.580000

3.110000

2.680000

3.580000

NULL

0.280000

NULL

*/

 

如果我们不想让分母为0的返回null的话，我们可以处理分母为1