求SQL语句小数后减少到2位
A表
ID SUM
1 10.0000002
2 10.010
3 15.050
4 18.1999999
...........
要求结果
ID SUM
1 10.00
2 10.01
3 15.10
4 18.20
...
数据库中不只这四条记录，还有很多，都需要改成保留小数后2位，求教各位大哥美女....
[解决办法]
select id,convert(decimal(9,2),[sum]) from a
[解决办法]
;with cte (id,num) as
(
select 1,10.0000002
union all select 2,10.010
union all select 3,15.050
union all select 4,18.1999999
)
select ID,cast(num as numeric(10,2)) as [sum] from cte
/*
IDsum
110.00
210.01
315.05
418.20
*/
;with cte (id,num) as
(
select 1,10.0000002
union all select 2,10.010
union all select 3,15.050
union all select 4,18.1999999
)
select ID,
sum=cast(num as decimal(10,2))
from cte
/*
IDsum
110.0000000
210.0100000
315.0500000
418.1900000
*/
create table #test
(id int identity,testPrice money)
go
insert into #test
select 10.0000002
union all select 2100.090
union all select 375.050
union all select 4108.1909999
go
select id,convert(numeric(10,2),testPrice) as testPrice from #test
create table #test
(id int identity,[SUM] money)
go
insert into #test
select 10.0000002
union all select 2100.090
union all select 375.050
union all select 4108.1909999
go
select id,convert(numeric(10,2),[SUM]) as [SUM] from #test
;with cte (id,num) as
(
select 1,10.0000002
union all select 2,10.010
union all select 3,15.050
union all select 4,18.1999999
)
select ID,convert(decimal(10,2),round(num,1)) as [sum] from cte
--结果
ID sum
----------- ---------------------------------------
1 10.00
2 10.00
3 15.10
4 18.20
(4 行受影响
with cte (id,num) as
(
select 1,10.0000002
union all select 2,10.010
union all select 3,15.050
union all select 4,18.1999999
)
select ID,cast([num] as decimal(10,2)) from cte
