在Select语句中返回带有小数位数的除数结果

Question

我正在尝试运行这个查询，但在进行数学计算后，它似乎没有正确地格式化数字。比例应该是2，但它不会像应该的那样显示。

SELECT 30 / 60 as Diff FROM Table

这将以0形式返回

SELECT Convert( Numeric(8,2), 30 / 60 ) as Diff FROM Table

这将以0.00形式返回

如何让它在需要时返回0.50？

Answer 1

你可以尝试以下方法，有几种方法可以实现。使用一个变量，或者只是在字段本身上执行CAST、CONVERT。

 SELECT (CAST(30 AS DECIMAL(8,2)) / CAST(60 AS DECIMAL(8,2))) as Diff FROM Table

 SELECT (CAST(30/60) AS DECIMAL(8,2)) as Diff FROM Table

 SELECT (30/60.0) as Diff FROM Table;

 SELECT CONVERT(DECIMAL(8,2), 30/60.0) as Diff FROM Table

请看一下这个MSDN article for further reference

Answer 2

在除法之前转换为数字，或者除以实数而不是整数。进行整数运算时，任何小数都会被丢弃。在删除分数后进行转换对您没有任何好处。

SELECT CONVERT(NUMERIC(8,2),30.0/60.0) AS DIFF FROM TABLE

或者如果选择列而不是使用数字

SELECT CONVERT(NUMERIC(8,2),columnA)/CONVERT(NUMERIC(8,2),columnB) AS DIFF FROM TABLE

(转换两者只是为了确保除法是根据我们想要的规则完成的)