请问，为何select 的两个值不一样呢？

better_cy 2010-02-08 03:05:37

Create table #temp
(
a numeric(20,10),
q numeric(20,4),
w numeric(20,4),
e numeric(20,4),
)

insert into #temp
select 1.6728333333,12,0,0
select a*(q-w) from #temp
select a*(q-w-e) from #temp

请问，为何select 的两个值不一样呢？

...全文

148 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

ChinaITOldMan 2010-02-09

打赏
举报

精度问题

xiejie831013 2010-02-09

打赏
举报

Many people are confused about SQL Server's precision and scale. This is unfortunate because choosing the correct values for precision and scale is critically important when you perform math operations using the decimal/numeric data type. The point of this blog is to explain how SQL Server determines the data type for math operations, and the order in which conversions occur.

For example:

tsqlLine number On/Off | Show/Hide | Select all
SELECT 10 / 3 UNION All
SELECT 10 / 3.0 UNION All
SELECT 10 / 3.00 UNION All
SELECT 10 / 3.000 UNION All
SELECT 10 / 3.0000 UNION All
SELECT 10 / 3.00000 UNION All
SELECT 10 / 3.000000 UNION All
SELECT 10 / 3.0000000 UNION All
SELECT 10 / 3.00000000
Let's take a close look at the above query so that we can predict the output. Of course, it will help if we know the data types that SQL Server uses. There is a relatively obscure function that you can use to determine the data types. SQL_VARIANT_PROPERTY

For the first calculation, we have 10 / 3. We all know the answer is 3 1/3, but how is this expressed in the output?

Well, using the SQL_VARIANT_PROPERTY function, we can determine the data type that SQL Server will use.

tsqlLine number On/Off | Show/Hide | Select all
SELECT SQL_VARIANT_PROPERTY(3, 'BaseType') AS [Base Type],
SQL_VARIANT_PROPERTY(3, 'Precision') AS [PRECISION],
SQL_VARIANT_PROPERTY(3, 'Scale') AS [Scale],

SQL_VARIANT_PROPERTY(10, 'BaseType') AS [Base Type],
SQL_VARIANT_PROPERTY(10, 'Precision') AS [PRECISION],
SQL_VARIANT_PROPERTY(10, 'Scale') AS [Scale]
The output indicates SQL Server will use Integer, Precision 10, scale 0. Using integer math, the output will be 3. Since both values are integer, the result is an integer.

Now, let's look at the next one. 10/3.0

tsqlLine number On/Off | Show/Hide | Select all
SELECT SQL_VARIANT_PROPERTY(3.0, 'BaseType') AS [Base Type],
SQL_VARIANT_PROPERTY(3.0, 'Precision') AS [PRECISION],
SQL_VARIANT_PROPERTY(3.0, 'Scale') AS [Scale],

SQL_VARIANT_PROPERTY(10, 'BaseType') AS [Base Type],
SQL_VARIANT_PROPERTY(10, 'Precision') AS [PRECISION],
SQL_VARIANT_PROPERTY(10, 'Scale') AS [Scale]
This time, we get Numeric(2,1) (for 3.0) and int for the 10. There are well defined (although obscure) rules for math operations. Full rules here: Precision, Scale, and Length

For division, the rule is:

Precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
Scale = max(6, s1 + p2 + 1)

p1 represents the precision of the first number. s1 represents the scale of the first number. P2 and S2 represent the second number.

10 / 3.0
P1 = 10
S1 = 0
P2 = 2
S2 = 1

Precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
Precision = 10 - 0 + 1 + max(6, 0 + 2 + 1)
Precision = 11 + Max(6, 3)
Precision = 11 + 6
Precision = 17

Unfortunately, this isn't correct because the SQL_VARIANT_PROPERTY is returning 10 for the integer. When dividing the numbers, SQL Server actually converts the integer to a decimal, using the smallest value possible to represent the value. In this case, 10 is converted to decimal(2,0). Performing the calculations again:

10 / 3.0
P1 = 2
S1 = 0
P2 = 2
S2 = 1

Precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
Precision = 2 - 0 + 1 + max(6, 0 + 2 + 1)
Precision = 3 + Max(6, 3)
Precision = 3 + 6
Precision = 9

Scale = max(6, s1 + p2 + 1)
Scale = max(6, 0 + 2 + 1)
Scale = max(6,3)
Scale = 6

So, Select 10 / 3.0 = 3.333333

Now, let's fast forward to the last calculation. Select 10 / 3.00000000

Precision/scale for the 10 (after converting to decimal) = 2,0
Precision/scale for the 3.00000000 = 9,8

10 / 3.00000000
P1 = 2
S1 = 0
P2 = 9
S2 = 8

Precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
Precision = 2 - 0 + 8 + max(6, 0 + 9 + 1)
Precision = 10 + Max(6, 10)
Precision = 10 + 10
Precision = 20

Scale = max(6, s1 + p2 + 1)
Scale = max(6, 0 + 9 + 1)
Scale = max(6,10)
Scale = 10

The result is 3.3333333333 Decimal(20,10)

Lastly, since the original example was a UNION ALL query, all results are converted to the same data type. Each result is first calculated, then finally converted to a data type that satisfies all results. In this case, each result is converted to a Decimal(20,10). But remember, this ONLY occurs after each calculation is performed!

Select 10 / 3 3.0000000000 Int
Select 10 / 3.0 3.3333330000 Decimal(9,6)
Select 10 / 3.00 3.3333330000 Decimal(10,6)
Select 10 / 3.000 3.3333330000 Decimal(11,6)
Select 10 / 3.0000 3.3333330000 Decimal(12,6)
Select 10 / 3.00000 3.3333333000 Decimal(14,7)
Select 10 / 3.000000 3.3333333300 Decimal(16,8)
Select 10 / 3.0000000 3.3333333330 Decimal(18,9)
Select 10 / 3.00000000 3.3333333333 Decimal(20,10)

黄_瓜 2010-02-08

打赏
举报

Create table #temp 

( 

a numeric(20,10), 

q numeric(10,4), --看这里 沟沟正解、沟沟正解、沟沟正解

w numeric(5,4), 

e numeric(5,4), 

) 



insert into #temp 

select 1.6728333333,12,0,0 

select a*(q-w) from #temp 

select a*(q-w-e) from #temp 

/*

---------------------------------------

20.0739999996000000



(1 行受影响)





---------------------------------------

20.0739999996000000



(1 行受影响)

playwarcraft 2010-02-08

打赏
举报



--可用下述方式，测试玩

declare @n1 numeric(28,10), @n2 numeric(28,4), @n  sql_variant



select  @n1=1.23456789 , @n2=0.0001

select @n=@n1*@n2

select [value]=@n,

          [type]=sql_variant_property(@n,'basetype') ,

         [precision]=sql_variant_property(@n,'precision') ,

         [scale]=sql_variant_property(@n,'scale') 



/*

value           type    precision        scale

---------------------------------------------------

0.000123	numeric	38	6



*/