求一sql 计算某个段的值,该如何解决_Sql Server

求一sql 计算某个段的值
表数据如下

ID GradeRange
1 0,999
2 1000，9999
3 10000,99999
4 100000，-1

其中-1代表无穷大

现求已解决方案存储过程函数。。不限

使得一个数值丢进去，判断是否在根据GradeRange最小值和最大值之间，从而计算相应的ID

比如 555 555>0 555<999 那么该ID 是1

------解决方案--------------------

SQL code

If object_id('tb') is not null     Drop table tbGoCreate table tb(ID int,GradeRange varchar(20))GoInsert into tbselect 1,'0,999' union allselect 2,'1000,9999' union allselect 3,'10000,99999' union allselect 4,'100000,-1' Godeclare @i intset @i=100009Select *from tbwhere @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 行）*/
------解决方案--------------------
SQL codedeclare @table table (ID int,GradeRange varchar(20))insert into @tableselect 1,'0,999' union allselect 2,'1000,9999' union allselect 3,'10000,99999' union allselect 4,'100000,-1'declare @i int;set @i=2500 --25000  --25000select ID from @tablewhere @i+1>=left(GradeRange,charindex(',',GradeRange)-1)+1 and@i+1<=right(GradeRange,len(GradeRange)-charindex(',',GradeRange))+1union all select top 1 4 from @table where @i>100001
------解决方案--------------------
建议表改成3个字段的，把后面两个上下限，分成2个字段，会容易控制很多。
------解决方案--------------------
探讨

建议表改成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)