四捨五入不進位??

人客反應一個四捨五入到小數二位的問題...
我沒什麼頭緒要怎麼解決, 但在不小心中發現一個方法,暫時可以解決四捨五入不進位的問題
這是sql server 的float bug嗎, 還是本來就這樣??

二組數字分別是1.055, 1.1243,型態是float
依四捨五入到小數第二位的話,  結果應該是  c1 =  1.06  c2= 1.12

但結果卻不如人意,而且只有當此float 刪好只有到小數第三位時,欲四捨五入進位到小數二位才會有問題

先reproduce一下狀況..

declare @tab table (c1 float,c2 float)
insert into @tab
select 1.055, 1.1243

select 
c1,c2
,cast(c1 as decimal(5,2)) as r1,cast(c2 as decimal(5,2)) as r2
,cast(c1 + 0.001 as decimal(5,2)) as t1,cast(c2 + 0.001 as decimal(5,2)) as t2
,cast(c1 + 0.0001 as decimal(5,2)) as s1,cast(c2 + 0.0001 as decimal(5,2)) as s2
 from @tab
但結果如下

c1	c2	r1	r2	t1	t2	s1	s2
1.055	1.124	1.05	1.12	1.06	1.13	1.06	1.12

有趣的是, 1.124 + 0.001 =  1.125 , 轉成decimal(5,2)後,又很精準的四捨五入了, 但結果是不對的....


所以再做下列測試
如果c1為1.0551 又或者將型態改成decimal(5,4)  則結果是正確的

declare @tab table (c1 float,c2 float)
insert into @tab
select 1.0551, 1.124

select  
c1,c2
,cast(c1 as decimal(5,2)) as r1,cast(c2 as decimal(5,2)) as r2
,cast(c1 + 0.001 as decimal(5,2)) as t1,cast(c2 + 0.001 as decimal(5,2)) as t2
,cast(c1 + 0.0001 as decimal(5,2)) as s1,cast(c2 + 0.0001 as decimal(5,2)) as s2
 from @tab

c1	c2	r1	r2	t1	t2	s1	s2
1.0551	1.124	1.06	1.12	1.06	1.13	1.06	1.12

declare @tab table (c1 decimal(5,4),c2 decimal(5,4))
insert into @tab
select 1.055, 1.124

select  
c1,c2
,cast(c1 as decimal(5,2)) as r1,cast(c2 as decimal(5,2)) as r2
,cast(c1 + 0.001 as decimal(5,2)) as t1,cast(c2 + 0.001 as decimal(5,2)) as t2
,cast(c1 + 0.0001 as decimal(5,2)) as s1,cast(c2 + 0.0001 as decimal(5,2)) as s2
 from @tab

c1	c2	r1	r2	t1	t2	s1	s2
1.055	1.124	1.06	1.12	1.06	1.13	1.06	1.12

將小數點變成四位後,四捨五入到小數二位就對了

再來一個測試

declare @tab table  (c1 float,c2 float)
 insert into @tab
select 1.055, 1.124

select  
c1,c2
,cast(c1+0.000 as decimal(5,2)) as r1,cast(c2 as decimal(5,2)) as r2
,cast(c1 + 0.001 as decimal(5,2)) as t1,cast(c2 + 0.001 as decimal(5,2)) as t2
,cast(c1 + 0.0001 as decimal(5,2)) as s1,cast(c2 + 0.0001 as decimal(5,2)) as s2
 from @tab

c1	c2	r1	r2	t1	t2	s1	s2
1.055	1.124	1.05	1.12	1.06	1.13	1.06	1.12

加上0.000是沒有用的...

結論:

1.float使用真的要小心謹慎,不是那麼精準

2.四捨五入到小數二位, 則先加0.0001變成有小數四位後,再進行四捨五入

大家有看懂嗎???