日期:2014-05-18  浏览次数:20544 次

求关于小数点进位的函数
要求将数值中小数点第三位只要不为0 就要进位并且保留2位小数点

SQL code

create table #abc
(num decimal(14,6) null)

insert into #abc
(num)
select 123.211432
union
select 123.219103
union
select 123.210921




要求结果:
123.22
123.22
123.21

------解决方案--------------------
SQL code

create table #abc
(num decimal(14,6) null)

insert into #abc
(num)
select 123.211432
union
select 123.219103
union
select 123.210921

select 
ceiling(cast(LEFT(LTRIM(num),
charindex('.',ltrim(num))+3) as decimal(14,3))*100)/100
as num
from #abc

/*
num
-------------------
123.210000
123.220000
123.220000
*/

------解决方案--------------------
SQL code

create table #abc
(num decimal(14,6) null)

insert into #abc
(num)
select 123.211432
union
select 123.219103
union
select 123.210921

select
    ceiling(cast(floor(num*1000)/10.0 as float))/100
from #abc
/*
(无列名)
123.21
123.22
123.22
*/

------解决方案--------------------
create table #abc
(num decimal(14,6) null)

insert into #abc
(num)
select 123.211432
union
select 123.219103
union
select 123.210921

select * , case when '0' = substring( cast ( num as varchar) , charindex ( '.' ,cast ( num as varchar) ) +3 ,1)
then cast ( num as decimal (12,2 ) ) else cast( ceiling (num * 100)/100. as decimal(12,2) ) end

from #abc