要求将数值中小数点第三位只要不为0 就要进位并且保留2位小数点
- SQL code
create table #abc(num decimal(14,6) null)insert into #abc(num)select 123.211432unionselect 123.219103unionselect 123.210921
要求结果：
123.22
123.22
123.21
------解决方案--------------------
- SQL code
create table #abc(num decimal(14,6) null)insert into #abc(num)select 123.211432unionselect 123.219103unionselect 123.210921select ceiling(cast(LEFT(LTRIM(num),charindex('.',ltrim(num))+3) as decimal(14,3))*100)/100as numfrom #abc/*num-------------------123.210000123.220000123.220000*/
------解决方案--------------------
- SQL code
create table #abc(num decimal(14,6) null)insert into #abc(num)select 123.211432unionselect 123.219103unionselect 123.210921select ceiling(cast(floor(num*1000)/10.0 as float))/100from #abc/*(无列名)123.21123.22123.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