id province from_pos to_pos level
1 浙江 0 60 1
2 浙江 60 120 2
3 浙江 120 200 3
4 浙江 200 230 4根据区间值确定一条数据例如 区间值是70 那获得ID=2的数据 from_pos(为>)to_pos(为<=)
问题:
如何确定结束值.如果只判断开始值top 1那当区间值正好为to_pos最大值时如何处理(例如区间值是120,获得ID为2的值)
1 浙江 0 60 1
2 浙江 60 120 2
3 浙江 120 200 3
4 浙江 200 230 4根据区间值确定一条数据例如 区间值是70 那获得ID=2的数据 from_pos(为>)to_pos(为<=)
问题:
如何确定结束值.如果只判断开始值top 1那当区间值正好为to_pos最大值时如何处理(例如区间值是120,获得ID为2的值)
----------------------------------------------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-09-17 11:04:06
-- Version:
-- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
-- Oct 14 2005 00:33:37
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([id] int,[province] varchar(4),[from_pos] int,[to_pos] int,[level] int)
insert [test]
select 1,'浙江'0,60,1 union all
select 2,'浙江',60,120,2 union all
select 3,'浙江',120,200,3 union all
select 4,'浙江',200,230,4
godeclare @a int
set @a=70
select * from test where @a >[from_pos] and @a<=[to_pos]/*
id province from_pos to_pos level
----------- -------- ----------- ----------- -----------
2 浙江 60 120 2(1 行受影响)
declare @a int
set @a=120
select * from test where @a >[from_pos] and @a<=[to_pos]/*
id province from_pos to_pos level
----------- -------- ----------- ----------- -----------
2 浙江 60 120 2(1 行受影响)
*/
*/
set @dis=120
select top 1 * from [tb]
where @dis>from_pos and @dis<=to_pos
order by id
declare @test table(id int, province nvarchar(2), from_pos int, to_pos int, level int)
insert into @test
select 1, N'浙江', 0, 60, 1 union all
select 2, N'浙江', 60, 120, 2 union all
select 3, N'浙江', 120, 200, 3 union all
select 4, N'浙江', 200, 230, 4declare @begin int
set @begin=120select * from @test
where @begin between from_pos and to_pos and ((from_pos>=@begin and to_pos<@begin) or (from_pos<@begin and to_pos>=@begin))
现在有个问题
一个省 最后一条记录的 from_pos是-1代表无穷大。不知道如何去比较
例如我以上表添加一条
5 浙江 230 -1 5当我输入231或者正无穷时查到该条记录
----------------------------------------------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-09-17 11:04:06
-- Version:
-- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
-- Oct 14 2005 00:33:37
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([id] int,[province] varchar(4),[from_pos] int,[to_pos] int,[level] int)
insert [test]
select 1,'浙江',0,60,1 union all
select 2,'浙江',60,120,2 union all
select 3,'浙江',120,200,3 union all
select 4,'浙江',200,230,4 union all
select 5,'浙江',230,-1,5
godeclare @a int
set @a=5000
select * from test where @a >[from_pos] and @a<=case when [to_pos]=-1 then @a end/*
id province from_pos to_pos level
----------- -------- ----------- ----------- -----------
5 浙江 230 -1 5(1 行受影响)*/
--其实where后面我们只要想办法构造为true的条件就好了。一个数任何情况都会小于等于本身,始终返回true
set @dis=1200
select top 1 * from [tb]
where @dis>from_pos and @dis<=case when to_pos=-1 then @dis else to_pos end
order by level