表数据如下
ID GradeRange
1 0,999
2 1000，9999
3 10000,99999
4 100000，-1
其中-1代表无穷大
现求已解决方案 存储过程 函数。。不限
使得一个数值丢进去 ，判断是否在根据GradeRange最小值和最大值之间，从而计算相应的ID
比如 555 555>0 555<999 那么该ID 是1
------解决方案--------------------
If object_id('tb') is not null
Drop table tb
Go
Create table tb(ID int,GradeRange varchar(20))
Go
Insert into tb
select 1,'0,999' union all
select 2,'1000,9999' union all
select 3,'10000,99999' union all
select 4,'100000,-1'
Go
declare @i int
set @i=100009
Select *
from tb
where @i>=cast(left(graderange,charindex(',',graderange)-1) as int)
and @i<=case when stuff(graderange,1,charindex(',',graderange),'')='-1' then @i+1
else cast(stuff(graderange,1,charindex(',',graderange),'') as int) end
/*
ID GradeRange
----------- --------------------
4 100000,-1
（所影响的行数为 1 行）
*/------解决方案--------------------
declare @table table (ID int,GradeRange varchar(20))
insert into @table
select 1,'0,999' union all
select 2,'1000,9999' union all
select 3,'10000,99999' union all
select 4,'100000,-1'
declare @i int;set @i=2500 --25000 --25000
select ID from @table
where @i+1>=left(GradeRange,charindex(',',GradeRange)-1)+1 and
@i+1<=right(GradeRange,len(GradeRange)-charindex(',',GradeRange))+1
union all select top 1 4 from @table where @i>100001
------解决方案--------------------
建议表改成3个字段的，把后面两个上下限，分成2个字段，会容易控制很多。
------解决方案--------------------
是的
------解决方案--------------------
declare @i
set @i=555
select id
from T
where @i between cast(substring(GradeRange,1,(CHARINDEX(',', GradeRange)) as integer) and cast(substring(GradeRange,CHARINDEX(',', GradeRange)+1 , len(GradeRange)) as integer)