sql语句 除法

Question

现在我通过sql语句实现了一下功能
id count1 count2
1   2      3
2   null   2
3   4      3
4   2      null
现在我需要在这个表的基础上实现就是增长率的运算，就是把（count2-count1）/count1;
运算的结果出来以后形成一张新的表格，类似于
id count1 count2  increase
1   2       3       0.5
。  。      。       。
。  。      。       。
。  。      。       。要考虑除数为空或者为0的情况
求帮助

Answer 1

USE tempdb
GO
CREATE TABLE test
(
id int ,
count1 decimal ,
count2 decimal
)INSERT INTO test
SELECT 1, 2 ,3
UNION ALL 
SELECT 2 ,NULL, 2
UNION ALL 
SELECT 3, 4, 3
UNION ALL 
SELECT 4 ,2 ,null
SELECT id,count1,count2,CONVERT(decimal(4,2),(ISNULL(count1,0)+ISNULL(count2,0))/ISNULL(count1,1))
 FROM test
 /*
 id          count1                                  count2                                  
----------- --------------------------------------- --------------------------------------- ---------------------------------------
1           2                                       3                                       2.50
2           NULL                                    2                                       2.00
3           4                                       3                                       1.75
4           2                                       NULL                                    1.00(4 行受影响) 
 */

Answer 2

SELECT id,count1,count2,CONVERT(decimal(4,2),(ISNULL(count2,0)-ISNULL(count1,0))/(case when ISNULL(count1,0)=0 then 1 else count1 end))
 FROM test

Answer 3

select count1,count2,(convert(float,count2)-convert(float,count1))/convert(float,count1) increase
 from table