如何查找遗漏的年份
现在有张表 比方说有两列
A 2005
A 2005
A 2007
A 2007
A 2007
A 2008

怎么查找数据A遗漏的2006年
[最优解释]

create table tb(col1 varchar(1),col2 int)
insert into tb
select 'A',2005 union all
select 'A',2005 union all
select 'A',2007 union all
select 'A',2007 union all
select 'A',2007 union all
select 'A',2008 

declare @max int,@min int
select @max=max(col2),@min=min(col2) from tb
select @min+number
 from master..spt_values left join tb on @min+number=col2
where type='p' and @min+number<@max  and col2 is null

/*
-----------
2006

(1 row(s) affected)

[其他解释]

--1有局限性，当间隔为两年时有问题
create table test(a char(1),b int)
insert into test select 'A',2003 union all
                select 'A',2005 union all
                select 'A',2007 union all
                select 'A',2007 union all
                select 'A',2007 union all
                select 'A',2008  

;with cte
as
(
select a,b,row_number() over(order by b) id,max(b) max_val from test group by a ,b
)
select  c.b+1 as min_lose from cte c 
where not exists(select 1 from cte c1 where c.id=c1.id+1 and c.b=c1.b+1) and (c.id+1) <(select max(id) from cte) 


--2 直接建个有序的年代数据，然后与此比较

[其他解释]

if object_id('tb')is not null
drop table tb
go
create table tb(cname varchar(1),cdate int)
insert into tb
select 'A',2005 union all
select 'A',2005 union all
select 'A',2007 union all
select 'A',2007 union all
select 'A',2007 union all
select 'A',2008 
 
declare @maxdate int,@mindate int
set @maxdate=(select max(cdate) from tb)
set @mindate=(select min(cdate) from tb)

select @mindate+number as cdate
 from master..spt_values 
where type='p' and number>0 and @mindate+number<@maxdate and  @mindate+number not in(select cdate from tb)

[其他解释]
自己顶一下 别沉了 希望高手解答
[其他解释]
谢谢各位，问题已经解决