日期:2014-05-17 浏览次数:20620 次
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