请各们帮帮忙?怎样判断PRICE例后面的两位小数的个位数.
判断1.假如小数点的个位数0.01至0.03,个位数就去除.
判断2.假如小数点的个位数0.04至0.05个位数就等于0.05.
判断3.假如小数点的个位数0.06至0.07个位数就等于0.05.
判断4.假如小数点的个位数0.08至0.09个位数就向十位数进1
price 结果
1.01 1
1.02 1
1.03 1
1.04 1.05
1.05 1.05
1.06 1.05
1.07 1.05
1.08 1.1
1.09 1.1
1.18 1.2
1.16 1.15
判断1.假如小数点的个位数0.01至0.03,个位数就去除.
判断2.假如小数点的个位数0.04至0.05个位数就等于0.05.
判断3.假如小数点的个位数0.06至0.07个位数就等于0.05.
判断4.假如小数点的个位数0.08至0.09个位数就向十位数进1
price 结果
1.01 1
1.02 1
1.03 1
1.04 1.05
1.05 1.05
1.06 1.05
1.07 1.05
1.08 1.1
1.09 1.1
1.18 1.2
1.16 1.15
insert into @t select 1.00
union all select 1.01
union all select 1.02
union all select 1.03
union all select 1.04
union all select 1.05
union all select 1.06
union all select 1.07
union all select 1.08
union all select 1.09
union all select 1.18
union all select 1.26select convert(decimal(10,2),(case when price*10-floor(price*10)<=0.3 then floor(price*10)/10 else
(case when price*10-floor(price*10)<=0.7 then floor(price*10)/10+0.05 else floor(price*10)/10+0.1 end)end))
from @t
/*
---------------------------------------
1.00
1.00
1.00
1.00
1.05
1.05
1.05
1.05
1.10
1.10
1.20
1.25
*/
insert into @T select 1.01
union all select 1.02
union all select 1.03
union all select 1.04
union all select 1.05
union all select 1.06
union all select 1.07
union all select 1.08
union all select 1.09
union all select 1.18
union all select 1.16SELECT
PRICE,
CASE
WHEN '0.0'+RIGHT(PRICE,1) BETWEEN '0.01' AND '0.03' THEN LEFT(PRICE,LEN(PRICE)-1)
WHEN '0.0'+RIGHT(PRICE,1) BETWEEN '0.04' AND '0.07' THEN LEFT(PRICE,LEN(PRICE)-1)+'5'
WHEN '0.0'+RIGHT(PRICE,1) BETWEEN '0.08' AND '0.09' THEN LEFT(PRICE+0.1,LEN(PRICE)-1)
END as [结果]
FROM @T
/**
PRICE 结果
------------ ---------
1.01 1.0
1.02 1.0
1.03 1.0
1.04 1.05
1.05 1.05
1.06 1.05
1.07 1.05
1.08 1.1
1.09 1.1
1.18 1.2
1.16 1.15
**/
select case when price*10-floor(price*10) between 0.1 and 0.3 then floor(price*10)*0.1
when price*10-floor(price*10) between 0.4 and 0.7 then floor(price*10)*0.1+0.05
when price*10-floor(price*10) between 0.8 and 0.9 then cast(price as decimal(12,1)) end
from tableName
create table #t
(
price decimal(12,2)
)insert #t select 1.01
insert #t select 1.02
insert #t select 1.03
insert #t select 1.04
insert #t select 1.05
insert #t select 1.06
insert #t select 1.07
insert #t select 1.08
insert #t select 1.09
insert #t select 1.18
insert #t select 1.16 select case when price*10-floor(price*10) between 0.1 and 0.3 then floor(price*10)*0.1
when price*10-floor(price*10) between 0.4 and 0.7 then floor(price*10)*0.1+0.05
when price*10-floor(price*10) between 0.8 and 0.9 then cast(price as decimal(12,1)) end
from #t
(case right(price,1)
when '0' then convert(char,convert(integer,price))
when '1' then convert(char,convert(integer,price))
when '2' then convert(char,convert(integer,price))
when '3' then convert(char,convert(integer,price))
when '4' then convert(char,convert(integer,price)+0.05)
when '5' then convert(char,convert(integer,price)+0.05)
when '6' then convert(char,convert(integer,price)+0.05)
when '7' then convert(char,convert(integer,price)+0.05)
else convert(char,convert(integer,price)+0.10)
end)
as price from table1
declare @t table(price decimal(10,2))
insert into @t select 1.00
union all select 1.01
union all select 1.02
union all select 1.03
union all select 1.04
union all select 1.05
union all select 1.06
union all select 1.07
union all select 1.08
union all select 1.09
union all select 1.18
union all select 1.16
select price,
case when Price%0.1 between 0.00 and 0.03 then round(Price,1,1)
when Price%0.1 between 0.04 and 0.07 then round(Price,1,1)+0.05
when Price%0.1 between 0.08 and 0.09 then round(Price,1,1)+0.1
end as 显示
from @T(12 行受影响)
price 显示
--------------------------------------- ---------------------------------------
1.00 1.00
1.01 1.00
1.02 1.00
1.03 1.00
1.04 1.05
1.05 1.05
1.06 1.05
1.07 1.05
1.08 1.10
1.09 1.10
1.18 1.20
1.16 1.15(12 行受影响)