如何在SQL Server查询中将整数转换为小数?

A column height is integer type in my SQL Server table. I want to do a division and have the result as decimal in my query:

在我的SQL Server表中，列高度是整数类型。我想做一个除法，在我的查询中有一个小数:

Select (height/10) as HeightDecimal

How do I cast so that the HeightDecimal is no longer integer? Thanks.

如何使HeightDecimal不再是整数呢?谢谢。

4 个解决方案

#1

SELECT height/10.0 AS HeightDecimal FROM dbo.whatever;

If you want a specific precision scale, then say so:

如果你想要一个精确的比例，那就说:

SELECT CONVERT(DECIMAL(16,4), height/10.0) AS HeightDecimal
  FROM dbo.whatever;

#2

SELECT CAST(height AS DECIMAL(18,0)) / 10

Edit: How this works under the hood?

编辑:如何在引擎盖下工作?

The result type is the same as the type of both arguments, or, if they are different, it is determined by the data type precedence table. You can therefore cast either argument to something non-integral.

结果类型与两个参数的类型相同，或者，如果它们不同，则由数据类型优先表决定。因此，你可以把这两个参数都转换成非整数。

Now DECIMAL(18,0), or you could equivalently write just DECIMAL, is still a kind of integer type, because that default scale of 0 means "no digits to the right of the decimal point". So a cast to it might in different circumstances work well for rounding to integers - the opposite of what we are trying to accomplish.

现在十进制(18,0)，或者你可以写成DECIMAL，仍然是一种整数类型，因为默认的0标度表示“小数点右边没有数字”。因此，在不同的情况下，对它的强制转换对于四舍五入到整数来说可能是有效的——这与我们要完成的相反。

However, DECIMALs have their own rules for everything. They are generally non-integers, but always exact numerics. The result type of the DECIMAL division that we forced to occur is determined specially to be, in our case, DECIMAL(29,11). The result of the division will therefore be rounded to 11 places which is no concern for division by 10, but the rounding becomes observable when dividing by 3. You can control the amount of rounding by manipulating the scale of the left hand operand. You can also round more, but not less, by placing another ROUND or CAST operation around the whole expression.

然而，小数有它们自己的规则。它们通常不是整数，但总是精确的数字。我们强制执行的十进制除法的结果类型是，在我们的例子中，是十进制(29,11)。因此，除法的结果将四舍五入到11个位置，这与除法10无关，但除以3时，四舍五入可以观察到。您可以通过操作左手操作数的比例来控制舍入的数量。您还可以通过在整个表达式周围放置另一个圆形或强制转换操作来增加而不是减少。

Identical mechanics governs the simpler and nicer solution in the accepted answer:

相同的力学支配着被接受的答案中更简单和更好的解:

  SELECT height / 10.0

In this case, the type of the divisor is DECIMAL(3,1) and the type of the result is DECIMAL(17,6). Try dividing by 3 and observe the difference in rounding.

在这种情况下，除数的类型是DECIMAL(3,1)，结果的类型是DECIMAL(17,6)。试着除以3，观察四舍五入的不同。

If you just hate all this talk of precisions and scales, and just want SQL server to perform all calculations in good old double precision floating point arithmetics from some point on, you can force that, too:

如果您讨厌所有这些关于精确和缩放的讨论，并且希望SQL server从某个点开始执行所有老式的双精度浮点算术，那么您也可以强制这样做:

SELECT height / CAST(10 AS FLOAT(53))

or equivalently just

或者说只是

SELECT height / CAST (10 AS FLOAT)

#3

You can either cast Height as a decimal:

您可以将高度转换为小数:

select cast(@height as decimal(10, 5))/10 as heightdecimal

or you place a decimal point in your value you are dividing by:

或者你在你的值里放一个小数点然后除以:

declare @height int
set @height = 1023

select @height/10.0 as heightdecimal

see sqlfiddle with an example

参见sqlfiddle示例

#4

select cast (height as decimal)/10 as HeightDecimal

#1

SELECT height/10.0 AS HeightDecimal FROM dbo.whatever;