返回 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 文章以获取进一步参考：

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

（转换两者只是为了确保划分是根据我们想要的规则完成的）