SQL Server一分为二 [英] SQL Server round after division

问题描述

在存储过程中，我有一个表达式，例如

In a stored procedure I have an expression like

select @some_val_in_percents = (@total_val / 100) * @some_val

如果我使用 Round 函数，例如：

If I use the Round function like this:

select @some_val_in_percents = Round((@total_val / 100) * @some_val, 0)

将在计算整个表达式时将结果四舍五入，或者将（@ total_val / 100）会四舍五入，然后乘以 @some_val ？

will the result be rounded when the whole expression is calculated or will (@total_val / 100) be rounded and than multiplied by @some_val?

推荐答案

您似乎在错误地计算百分比值。这是我希望的样子：

You seem to be calculating the percent value wrongly. Here's what I would expect it to be like:

@some_val * 100 / @total_val

对于 ROUND（）函数，它将作用于最终结果表达式，而不是部分结果。因此，首先对表达式进行完全求值，然后对结果应用 ROUND（）。

As for the ROUND() function, it will act on the final result of the expression, not on the partial result. So, first the expression is evaluated completely, then ROUND() is applied to the result.

请注意（如果您还不知道的话）如果除法运算符的两个操作数都是整数，SQL Server将执行整数除法，即除法的结果将向下舍入到最接近的整数即使之前 ROUND（）也适用。如果要避免这种情况，请确保至少一个操作数不是整数，例如像这样：

Note also (in case you haven't already known it) that if both operands of the division operator are integers, SQL Server will perform an integer division, i.e. the result of the division would be rounded down to the nearest integer even before ROUND() is applied. If you want to avoid that, make sure that at least one of the operands is not integer, e.g. like this:

ROUND(@some_val * 100.0 / @total_val, 2)

还要注意第二个参数（精度），这在 Transact-SQL ROUND（） 。

Note also the second argument (precision), which is required in Transact-SQL ROUND().

这篇关于SQL Server一分为二的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！