为什么SQL中的数学运算返回0 ?

Question

为什么在下面的TSQL查询中没有计算CALC的值?

SELECT
    D1, D2, (D1+D2) As 'Sum', ((D1/(D1+D2))*100) As CALC
FROM
    (SELECT 3 As D1, 6 As D2) A

这是我得到的结果:

D1          D2          Sum         CALC
----------- ----------- ----------- -----------
3           6           9           0

Answer 1

integer除以integer生成一个整数，因此如果两个操作数都是整数，那么除法操作将截断结果的小数部分。将1更改为浮点数或小数来修复它。

运行Select 5 / 6, 5 / 6.0看看我是什么意思

用强制转换修复你的代码…

 SELECT  D1, D2, (D1+D2) As 'Sum', 
    ((D1/ Cast(D1+D2 as Float))*100) As CALC
 FROM (SELECT 3 As D1, 6 As D2) A

或者让值以float开头:

 SELECT  D1, D2, (D1+D2) As 'Sum', 
    ((D1/ (D1+D2))*100) As CALC
 FROM (SELECT 3.0 As D1, 6.0 As D2) A

Answer 2

整数除法四舍五入为零
移动100。你仍然会得到一些四舍五入。
如果你不能容忍任何舍入，那么就不要使用整数。

SELECT
    D1, D2, (D1+D2) As 'Sum', D1/(D1+D2) as frac, (D1*100)/(D1+D2) As pct
FROM
    (SELECT 3 As D1, 6 As D2) A