SQL中的小数值，用于划分结果

In SQL, I have col1 and col2. Both are integers.

在SQL中，我有col1和col2。都是整数。

I want to do like:

我想做的是:

select col1/col2 from tbl1

I get the result 1 where col1=3 and col2=2

得到结果1，其中col1=3, col2=2

The result I want is 1.1

我想要的结果是1。1

I put round(col1/col2,2). The result is still 1.

我把圆(col1 / col2,2)。结果仍然是1。

I put decimal(col1/col2,2). The decimal is not built in function.

我把小数点(col1 / col2,2)。十进制不是函数内建的。

How can I do exactly to get 1.1?

怎么才能得到1。1呢?

5 个解决方案

#1

You will need to cast or convert the values to decimal before division. Take a look at this http://msdn.microsoft.com/en-us/library/aa226054.aspx

您需要在除法之前将值转换为十进制。看看这个http://msdn.microsoft.com/en-us/library/aa226054.aspx。

For example

例如

DECLARE @num1 int = 3 DECLARE @num2 int = 2

SELECT @num1/@num2

SELECT @num1/CONVERT(decimal(4,2), @num2)

The first SELECT will result in what you're seeing while the second SELECT will have the correct answer 1.500000

第一个SELECT将导致您所看到的内容，而第二个SELECT将有正确的答案为1.5万。

#2

Just another approach:

另一个方法:

SELECT col1 * 1.0 / col2 FROM tbl1

Multiplying by 1.0 turns an integer into a ~~float~~ numeric(13,1) and so works like a typecast, but most probably it is slower than that.

乘以1.0将一个整数变成一个浮点数(13,1)，因此它的工作方式类似于一个类型广播，但它很可能比这个要慢。

A slightly shorter variation suggested by Aleksandr Fedorenko in a comment:

亚历山大·费多连科(Aleksandr Fedorenko)在评论中建议的稍短一些的变化:

SELECT col1 * 1. / col2 FROM tbl1

The effect would be basically the same. The only difference is that the multiplication result in this case would be numeric(12,0).

效果基本上是一样的。唯一的区别是乘法结果是数值的(12,0)。

Principal advantage: less wordy than other approaches.

主要优点:不像其他方法那样冗长。

#3

SELECT CAST (col1 as float) / col2 FROM tbl1

从tbl1中选择CAST (col1作为float) / col2

One cast should work. ("Less is more.")

一个演员应该工作。(“少即是多”。)

From Books Online:

从图书在线:

Returns the data type of the argument with the higher precedence. For more information about data type precedence, see Data Type Precedence (Transact-SQL).

返回优先级较高的参数的数据类型。有关数据类型优先级的更多信息，请参见数据类型优先级(Transact-SQL)。

If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated

如果一个整数被除数除以一个整数除数，那么结果就是一个整数，这个整数的小数部分被截断

#4

There may be other ways to get your desired result.

也许有其他的方法可以得到你想要的结果。

Declare @a int
Declare @b int
SET @a = 3
SET @b=2
SELECT cast((cast(@a as float)/ cast(@b as float)) as float)

#5

CAST( ROUND(columnA *1.00 / columnB, 2) AS FLOAT)

#1