求关于小数点进位的函数

[crazy_boom]

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

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

[bean_sql]

楼主给的测试结果有问题吧 ？

[bean_sql]

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

insert into #abc(num)
select 123.211432
union
select 123.219103
union
select 123.210921
go
select CONVERT(numeric(10,2),num) from  #abc
/*

---------------------------------------
123.21
123.21
123.22

(3 row(s) affected)
*/

[crazy_boom]

要求的结果就是

123.22
123.22
123.21

要求1：结果为2位小数点
要求2：数值的第三位不为0则进位 为0 不进位

[开着拖拉机泡妞]

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
*/

[crazy_boom]

要求的结果就是

123.22
123.22
123.22

也可以这样：先进位到3位 然后计算第三位是否需要进位 123.210921 可以为123.22

要求1：结果为2位小数点
要求2：数值的第三位不为0则进位 为0 不进位

[Shawn]

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
*/

[bean_sql]

[Quote=引用 6 楼 的回复:]

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)……
[/Quote]

[弘恩]

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