SELECT * FROM TEST WHERE item_code=@item_code AND begin_value>=@begin_value AND end_value<=@end_value?
create table test(item_code varchar(60) , begin_value int, end_value int) insert into test(item_code,begin_value,end_value) select 'a001',100,200 union all select 'a001',260,460 union all select 'a001',600,699 union all select 'a001',600,1200 declare @code varchar(10),@begin_value int, @end_value int select @code='a001',@begin_value=230,@end_value=700 select ltrim(begin_value)+'-'+ltrim(end_value) as 短号区间 from test where item_code=@code and @begin_value < begin_value and end_value<@end_value--如果你没有描述错就是这样了 /* 短号区间 ------------------------- 260-460 600-699(2 行受影响) */ drop table test
可以示例没有说明实际数据会有交叉的情况,记录已使用号码表结构如下: create table test(item_code varchar(60) , begin_value int, end_value int) insert into test(item_code,begin_value,end_value) select 'a001',100,200 union select 'a001',260,460 union select 'a001',600,800如传入参数为:item_code,begin_value,end_value a001,230,700 则返回的重号区间是: 260-460 600-700已有处理思路,先将存在的号码段分解为表,再将传入的区间分解为表, 进行存在性查询,但感觉效率比较低!求有没有更好的处理方法.
create table test(item_code varchar(60) , begin_value int, end_value int) insert into test(item_code,begin_value,end_value) select 'a001',100,200 union select 'a001',260,460 union select 'a001',600,800 godeclare @code varchar(10), @begin_value int, @end_value int set @code='a001' set @begin_value=230 set @end_value=700 select case when @begin_value>begin_value then rtrim(@begin_value) else RTRIM(begin_value) end +'-'+case when @end_value<end_value then rtrim(@end_value) else RTRIM(end_value) end as[重复区] from test k where @begin_value<=end_value /* 重复区 ------------------------- 260-460 600-700*/
select case when @begin_value>begin_value then rtrim(@begin_value) else RTRIM(begin_value) end +'-'+case when @end_value<end_value then rtrim(@end_value) else RTRIM(end_value) end as[重复区] from test k where @begin_value<=end_value and item_code=@code
WHERE item_code=@item_code
AND begin_value>=@begin_value AND end_value<=@end_value?
begin_value int,
end_value int) insert into test(item_code,begin_value,end_value)
select 'a001',100,200
union all
select 'a001',260,460
union all
select 'a001',600,699
union all
select 'a001',600,1200 declare @code varchar(10),@begin_value int, @end_value int
select @code='a001',@begin_value=230,@end_value=700 select ltrim(begin_value)+'-'+ltrim(end_value) as 短号区间 from test where item_code=@code and @begin_value < begin_value and end_value<@end_value--如果你没有描述错就是这样了
/*
短号区间
-------------------------
260-460
600-699(2 行受影响)
*/
drop table test
create table test(item_code varchar(60) ,
begin_value int,
end_value int) insert into test(item_code,begin_value,end_value)
select 'a001',100,200
union
select 'a001',260,460
union
select 'a001',600,800如传入参数为:item_code,begin_value,end_value
a001,230,700 则返回的重号区间是:
260-460
600-700已有处理思路,先将存在的号码段分解为表,再将传入的区间分解为表,
进行存在性查询,但感觉效率比较低!求有没有更好的处理方法.
begin_value int,
end_value int) insert into test(item_code,begin_value,end_value)
select 'a001',100,200
union
select 'a001',260,460
union
select 'a001',600,800
godeclare @code varchar(10),
@begin_value int,
@end_value int
set @code='a001'
set @begin_value=230
set @end_value=700 select
case when @begin_value>begin_value then rtrim(@begin_value) else RTRIM(begin_value) end
+'-'+case when @end_value<end_value then rtrim(@end_value) else RTRIM(end_value) end as[重复区]
from test k
where @begin_value<=end_value
/*
重复区
-------------------------
260-460
600-700*/
case when @begin_value>begin_value then rtrim(@begin_value) else RTRIM(begin_value) end
+'-'+case when @end_value<end_value then rtrim(@end_value) else RTRIM(end_value) end as[重复区]
from test k
where @begin_value<=end_value and item_code=@code