500以下为0
500~1499为1
1500~2499为2
2500到3499为3
3500到4499为4
以此类推
就是N除以1000得到的商,余数大于500则加一,小于则不算。
如
123=0
560==1
1230=1
1501=2
2480=2
2560=3······
如何形成一个公式呢?
500~1499为1
1500~2499为2
2500到3499为3
3500到4499为4
以此类推
就是N除以1000得到的商,余数大于500则加一,小于则不算。
如
123=0
560==1
1230=1
1501=2
2480=2
2560=3······
如何形成一个公式呢?
set @n = 123
select @n/1000+(@n % 1000 + 1)/500
set @n = 560
select @n/1000+(@n % 1000 + 1)/500
set @n = 1230
select @n/1000+(@n % 1000 + 1)/500
set @n = 1501
select @n/1000+(@n % 1000 + 1)/500
set @n = 2480
select @n/1000+(@n % 1000 + 1)/500
set @n = 2560
select @n/1000+(@n % 1000 + 1)/500
set @n = 123
select @n/1000+(@n % 1000)/500
set @n = 560
select @n/1000+(@n % 1000)/500
set @n = 1230
select @n/1000+(@n % 1000)/500
set @n = 1501
select @n/1000+(@n % 1000)/500
set @n = 2480
select @n/1000+(@n % 1000)/500
set @n = 2560
select @n/1000+(@n % 1000)/500
set @n = 1499
select @n/1000+(@n % 1000)/500
select @n/1000+(@n % 1000)/500
insert into tb select 123 union all select 560 union all select 1230
union all select 1501 union all select 2480 union all select 2560
go
select (col/500+1)/2 from tb
go
drop table tb
/*
-----------
0
1
1
2
2
3(6 行受影响)
*/
set @i=2560
select case when @i<500 then 0 else @i/1000+(case when @i%1000>500 then 1 else 0 end) end
set @n=1526
if(@n%1000>=500)
begin
set @m=@n/1000+1
end
else
begin
set @m=@n/1000
end
select @n,@m
select @n/1000+(@n % 1000)/500
这个好 呵呵
use tempdb;
/*
create table tb
(
[content] int not null
);
insert into tb([content])
values
(123),(560),(1230),(1501),(2480),(2560);
*/
select *,
case when [content]%1000 > 500 then ([content]/1000 + 1) else ([content]/1000)
end as [得到的值]
from tb;