SQLServer，部门返回零

下面是我在示例中使用的代码:

 PRINT @set1
PRINT @set2


SET @weight= @set1 / @set2;
PRINT @weight

结果如下:

47
638
0

我想知道为什么它返回的是 0而不是 0,073667712

148513 次浏览
小开

Because it's an integer. You need to declare them as floating point numbers or decimals, or cast to such in the calculation.

小开

if you declare it as float or any decimal format it will display

0

only

E.g :

declare @weight float;


SET @weight= 47 / 638; PRINT @weight

Output : 0

If you want the output as

0.073667712

E.g 

declare @weight float;


SET @weight= 47.000000000 / 638.000000000; PRINT @weight
小开
最佳答案

Either declare set1 and set2 as floats instead of integers or cast them to floats as part of the calculation:

SET @weight= CAST(@set1 AS float) / CAST(@set2 AS float);
小开

When you use only integers in a division, you will get integer division. When you use (at least one) double or float, you will get floating point division (and the answer you want to get).

So you can

  1. declare one or both of the variables as float/double
  2. cast one or both of the variables to float/double.

Do not just cast the result of the integer division to double: the division was already performed as integer division, so the numbers behind the decimal are already lost.

小开

Simply mutiply the bottom of the division by 1.0 (or as many decimal places as you want)

PRINT @set1
PRINT @set2
SET @weight= @set1 / @set2 *1.00000;
PRINT @weight
小开

In SQL Server direct division of two integer returns integer even if the result should be the float. There is an example below to get it across:

--1--
declare @weird_number_float float
set @weird_number_float=22/7
select @weird_number_float


--2--
declare @weird_number_decimal decimal(18,10)
set @weird_number_decimal=22/7
select @weird_number_decimal


--3--
declare @weird_number_numeric numeric
set @weird_number_numeric=22/7
select @weird_number_numeric


--Right way


declare @weird_number float
set @weird_number=cast(22 as float)/cast(7 as float)
select @weird_number

Just last block will return the 3,14285714285714. In spite of the second block defined with right precision the result will be 3.00000.


提交答案