SQL 查询一个范围不再另一个范围内

[xiao504024133]

表内容大概如下:
firstnumber，lastnumber,id
10000000 20000000 1
20000001 29999999 2
问：如何查询10000001---19999999 这个范围在以上范围。
是做判断的，如果所举例子的范围是在表中有交集，则不能被插入。

[chuanzhang5687]

select * from tb where firstnumber>=10000001 and lastnumber<=19999999--这样？

[快溜]

select * from tb where firstnumber>10000001 and lastnumber>19999999

[快溜]

--
select * from tb where firstnumber<10000001 and lastnumber>19999999 

[xiao504024133]

谢谢各位，不过不是这样的，

[xiao504024133]

id firstnumber lastnumber
1 460099093170001 460099093170005
2 460099093170006 460099093170010
3 460029834290000 460029834290001
4 111111111111111 222222222222228
5 333333333333333 444444444444444
6 111111111111109 111111111111110
7 444444444444445 555555555555555
在页面输入一个范围，而该范围如果在以上范围内，则不能被插入。

请问如何写啊

[investruth]

  declare @b int 
  select @b=1 from tb where firstnumber>=10000001 and lastnumber<=19999999
  if(@b=1)
  begin
  print '不能插入' 
  end
  else
  begin)
  insert into 表名(列名)  values (值)
  end

[--小F--]

------------------------------------

-- Author: happyflystone   

-- Date:2009-07-20 

-- Parameter: @CardString 被查询的串，形如：13300000000-13300001234,13300002230,13300002300

--            @CardNo     要查询的串

-- Return   : int      0 -- 不存在于搜索串的范围内

--                     1 -- 存在于

-- 转载请注明出处。更多请访问： http://blog.csdn.net/happyflystone

------------------------------------

Create function IsInCardString(@CardString varchar(8000),@CardNo varchar(11)) 

returns int

as

begin

    declare @temp table(a varchar(200))

     

    declare @i int

    

    set @CardString = rtrim(ltrim(@CardString))+','

    set @i = charindex(',', @CardString)

    

    while @i >= 1

    begin

        insert @temp values(left(@CardString, @i - 1))

        

        set @CardString = substring(@CardString, @i + 1, len(@CardString) - @i)

        set @i = charindex(',', @CardString)

    end

    

    if exists(select 1

    from (

           select case when charindex('-',a) > 0 then left(a,11) else a end as s,

           case when charindex('-',a) > 0 then right(a,11) else a end as e

           from @temp

              ) a

    where @CardNo between s and e)

              set @i= 1

    else 

        set @i= 0

    return @i

end

go

 

 

declare @CardString varchar(1000)

set @CardString = '13300000000-13300001234,13300002230,13300002300,13300002302,13300004101-13300004204,13300004212,13300004310'

 

declare @CardNo varchar(1000)

set @CardNo = '13300000001'　--存在

select dbo.IsInCardString(@CardString,@CardNo)

set @CardNo = '13300001235'  --不存在

select dbo.IsInCardString(@CardString,@CardNo)

/*

 

            

----------- 

1

 

（所影响的行数为 1 行）

 

            

----------- 

0

 

（所影响的行数为 1 行）

 

*/

 

drop function IsInCardString



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/happyflystone/archive/2009/07/21/4365264.aspx

[yubofighting]

declare @min bigint
declare @max bigint
declare @min1 bigint
declare @max1 bigint
select @max1=max(firstnumber),id from tb group by id
select @min1=max(firstnumber),id from tb group by id
select @max2=max(lastnumber),id from tb group by id
select @max1=max(lastnumber),id from tb group by id
if @min1<=@min2 and @max1<=@max2
set @min=@min1
set @max=@max2
.....................

[cd731107]

--设@b>@a,否则调换一下@a和@b的位置 
insert tb(firstnumber,lastnumber)
select @a,@b
where not exists 
(select 1 from tb 
where (@a<=firstnumber  and @b>=firstnumber)
or    (@a>=firstnumber and @a<=firstnumber)