id code
1 1.1
2 1.31
3 1.6
4 1.82 5 2.1
6 2.6
7 6.8 code值2 以下,0.25一个段,例如1.6取1.75,1.76取2,1.25取1.25,1.1取1.25;
code值2 以上,0.5一个段, 例如2.2,,取2.5,5.6取6,6.5取6.5, 2.1取2.5。
得出的结果为: id code
1 1.25
2 1.50
3 1.75
4 2 5 2.5
6 3
7 7
1 1.1
2 1.31
3 1.6
4 1.82 5 2.1
6 2.6
7 6.8 code值2 以下,0.25一个段,例如1.6取1.75,1.76取2,1.25取1.25,1.1取1.25;
code值2 以上,0.5一个段, 例如2.2,,取2.5,5.6取6,6.5取6.5, 2.1取2.5。
得出的结果为: id code
1 1.25
2 1.50
3 1.75
4 2 5 2.5
6 3
7 7
update tb
set code = (case when code<=2 then code*100/25*0.25+(case when code*100%25>0 then 0.25 else 0 end)
when code>2 then code*10/5*0.5+(case when code*10%5>0 then 0.5 else 0 end) end)
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[code] decimal(18,2))
Insert #T
select 1,1.1 union all
select 2,1.31 union all
select 3,1.6 union all
select 4,1.82 union all
select 5,2.1 union all
select 6,2.6 union all
select 7,6.8
Go
Select [id],[code]=CAST(CEILING([code]*100.0/25)*25/100 AS DECIMAL(18,2))
from #T
/*
id code
1 1.25
2 1.50
3 1.75
4 2.00
5 2.25
6 2.75
7 7.00
*/
create table tb(id int,code decimal(12,2))
insert into tb
select 1,1.1 union all
select 2,1.31 union all
select 3,1.6 union all
select 4,1.82 union all
select 5,2.1 union all
select 6,2.6 union all
select 7,6.8
goupdate tb
set code = (case when code<=2 then cast(code*100 as int)/25*0.25+(case when code*100%25>0 then 0.25 else 0 end)
when code>2 then cast(code*10 as int)/5*0.5+(case when code*10%5>0 then 0.5 else 0 end) end)select * from tbdrop table tb/***********************id code
----------- ---------------------------------------
1 1.25
2 1.50
3 1.75
4 2.00
5 2.50
6 3.00
7 7.00(7 行受影响)
drop table tb
go
create table tb
(id int,code decimal(18, 2))
insert into tb values(1,1.1)
insert into tb values(2,1.31)
insert into tb values(3,1.6)
insert into tb values(4,1.82)
insert into tb values(5,2.1)
insert into tb values(6,2.6)
insert into tb values(7,6.8)--code值2 以下,0.25一个段,例如1.6取1.75,1.76取2,1.25取1.25,1.1取1.25;
-- code值2 以上,0.5一个段, 例如2.2,,取2.5,5.6取6,6.5取6.5, 2.1取2.5。select id,CAST(CEILING([code]*100.0/25)*25/100 AS DECIMAL(18,2))from tbid
----------- ---------------------------------------
1 1.25
2 1.50
3 1.75
4 2.00
5 2.25
6 2.75
7 7.00(7 行受影响)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[code] numeric(3,2))
insert [tb]
select 1,1.1 union all
select 2,1.31 union all
select 3,1.6 union all
select 4,1.82 union all
select 5,2.1 union all
select 6,2.6 union all
select 7,6.8
--------------开始查询--------------------------
/*
code值2 以下,0.25一个段,例如1.6取1.75,1.76取2,1.25取1.25,1.1取1.25;
code值2 以上,0.5一个段, 例如2.2,,取2.5,5.6取6,6.5取6.5, 2.1取2.5。
*/
select [id],[code]=case when [code]>1 then ceiling([code]*100/25)*25/100
else ceiling([code]*100/50)*50/100
end
from [tb]
----------------结果----------------------------
/*
id code
----------- ---------------------------------------
1 1.250000
2 1.500000
3 1.750000
4 2.000000
5 2.250000
6 2.750000
7 7.000000(7 行受影响)*/
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[code] numeric(3,2))
insert [tb]
select 1,1.1 union all
select 2,1.31 union all
select 3,1.6 union all
select 4,1.82 union all
select 5,2.1 union all
select 6,2.6 union all
select 7,6.8
--------------开始查询--------------------------
/*
code值2 以下,0.25一个段,例如1.6取1.75,1.76取2,1.25取1.25,1.1取1.25;
code值2 以上,0.5一个段, 例如2.2,,取2.5,5.6取6,6.5取6.5, 2.1取2.5。
*/
select [id],[code]=case when [code]<2 then ceiling([code]*100/25)*25/100
else ceiling([code]*100/50)*50/100
end
from [tb]
----------------结果----------------------------
/*
id code
----------- ---------------------------------------
1 1.250000
2 1.500000
3 1.750000
4 2.000000
5 2.500000
6 3.000000
7 7.000000(7 行受影响)*/
declare @T table (id int,code numeric(3,2))
insert into @T
select 1,1.1 union all
select 2,1.31 union all
select 3,1.6 union all
select 4,1.82 union all
select 5,2.1 union all
select 6,2.6 union all
select 7,6.8select id,code=
ceiling(code/(0.25*(1+sign(floor(code-1)))))*0.25*(1+sign(floor(code-1))) from @T
/*
id code
----------- ---------------------------------------
1 1.25
2 1.50
3 1.75
4 2.00
5 2.50
6 3.00
7 7.00
*/
case when code<2 then ceiling(code*4)/4
else ceiling(code*2)/2
end
from testtb
select id,
case when code<2 then ceiling(code*4)/4
else ceiling(code*2)/2
end
from testtb