2.1 ->2
2.3 ->2
2.5 ->2.5
2.6 ->2.5
2.8 ->2.5
3 ->3即向下精确到0.5 这个怎么设?
用 ROUND(2.3, 2),CEILING(2.3)等测试好久没搞定.
2.3 ->2
2.5 ->2.5
2.6 ->2.5
2.8 ->2.5
3 ->3即向下精确到0.5 这个怎么设?
用 ROUND(2.3, 2),CEILING(2.3)等测试好久没搞定.
调试欢乐多
select floor(@v+@v)/2
set @s = 2.1
select case when @s - floor(@s) >= 0.5 then floor(@s) + 0.5 else floor(@s) endset @s = 2.4
select case when @s - floor(@s) >= 0.5 then floor(@s) + 0.5 else floor(@s) endset @s = 2.5
select case when @s - floor(@s) >= 0.5 then floor(@s) + 0.5 else floor(@s) endset @s = 2.6
select case when @s - floor(@s) >= 0.5 then floor(@s) + 0.5 else floor(@s) endset @s = 2.9
select case when @s - floor(@s) >= 0.5 then floor(@s) + 0.5 else floor(@s) endset @s = 3
select case when @s - floor(@s) >= 0.5 then floor(@s) + 0.5 else floor(@s) end
with cte as (
select 2.1 as c1 union all
select 2.3 union all
select 2.5 union all
select 2.6 union all
select 2.8 union all
select 3
)select case when
convert( decimal(9,1),('0'+substring(convert(varchar(10),c1), charindex('.',convert(varchar(10),c1)),len(c1))))-0.5>=0 then
convert( decimal(9,1),substring(convert(varchar(10),c1),1,charindex('.',convert(varchar(10),c1))-1))+0.5 else
convert( decimal(9,1),substring(convert(varchar(10),c1),1,charindex('.',convert(varchar(10),c1))-1)) end
from cte---------------------------------------
2.0
2.0
2.5
2.5
2.5
3.0(6 行受影响)