查找表的缺失范围

有表如下

GO
/****** 对象:  Table [dbo].[T1]    脚本日期: 12/21/2008 22:29:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T1](
 [col1] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
 [col1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

insert into dbo.T1

 select 1 union all
 select 2 union all
 select 3 union all
 select 100 union all
 select 101 union all
 select 103 union all
 select 104 union all
 select 105 union all
 select 106

 

现在查找表的缺失范围

如以上表的缺失范围是

begn_col      end_col

4                  99

102             102

 如下是该问题的解决方法

 --author:cowge
------方法一
 select col1+1 as begin_col,
 end_col=(
     select min(col1) from dbo.T1 where col1>a.col1
    )-1
 from dbo.T1 as a
 where not exists
 (
    select * from dbo.T1 where col1=a.col1+1
 ) and col1<(select max(col1) from dbo.T1)

-----方法二

select cur+1 as begin_col,nxt-1 as end_col from
 (
    select col1 as cur,
    nxt=(
      select min(col1) from dbo.T1 where col1>a.col1
    ) from dbo.T1 as a

 ) t where nxt-cur>1