理论上是指定的ID每天都必须有记录,要查询第一次没有记录的日期。查询条件为一个日期范围
(如:2010-12-01至2010-12-08 结果为:2010-12-05;2010-12-11至2010-12-15 结果为:2010-12-11;2010-12-01至2010-12-03 结果为:null),表结构:id date
1 2010-12-01
1 2010-12-02
1 2010-12-03
1 2010-12-04
1 2010-12-08
1 2010-12-09
1 2010-12-10
2 2010-12-01
2 2010-12-02
2 2010-12-03
2 2010-12-04
2 2010-12-05
2 2010-12-06
2 2010-12-19
(如:2010-12-01至2010-12-08 结果为:2010-12-05;2010-12-11至2010-12-15 结果为:2010-12-11;2010-12-01至2010-12-03 结果为:null),表结构:id date
1 2010-12-01
1 2010-12-02
1 2010-12-03
1 2010-12-04
1 2010-12-08
1 2010-12-09
1 2010-12-10
2 2010-12-01
2 2010-12-02
2 2010-12-03
2 2010-12-04
2 2010-12-05
2 2010-12-06
2 2010-12-19
给你转一个http://topic.csdn.net/t/20040422/19/2997765.html
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[date] datetime)
insert [tb]
select 1,'2010-12-01' union all
select 1,'2010-12-02' union all
select 1,'2010-12-03' union all
select 1,'2010-12-04' union all
select 1,'2010-12-08' union all
select 1,'2010-12-09' union all
select 1,'2010-12-10' union all
select 2,'2010-12-01' union all
select 2,'2010-12-02' union all
select 2,'2010-12-03' union all
select 2,'2010-12-04' union all
select 2,'2010-12-05' union all
select 2,'2010-12-06' union all
select 2,'2010-12-19'
---查询---
declare @dt1 datetime,@dt2 datetime
set @dt1='2010-12-11'
set @dt2='2010-12-15'
select top 1 convert(varchar,dateadd(dd,a.number,@dt1),23)
from master..spt_values a
join (select distinct id from tb) b on 1=1
left join tb c on dateadd(dd,a.number,@dt1)=c.[date] and b.id=c.id
where a.type='P' and dateadd(dd,a.number,@dt1)<=@dt2
and b.id=1
and c.id is null---结果---------------------------------
2010-12-11(1 行受影响)
select * from ta right join tb on ta.date = tb.date 然后根据返回结果按照a.id 和b.date排序,并且查出第一条a的字段为空的就是你需要的了
3楼上的还有其他办法吗?spt_values 这个表没有操作权限。
--sql2005及以上版本用
declare @dt1 datetime,@dt2 datetime
set @dt1='2010-12-11'
set @dt2='2010-12-15'
select top 1 convert(varchar,dateadd(dd,a.number,@dt1),23)
from (select top 100 number=row_number() over(order by getdate())-1 from sys.columns,sys.objects) a
join (select distinct id from tb) b on 1=1
left join tb c on dateadd(dd,a.number,@dt1)=c.[date] and b.id=c.id
where dateadd(dd,a.number,@dt1)<=@dt2
and b.id=1
and c.id is null
select top 1000 number=identity(int,0,1) into # from syscolumns,sysobjects
godeclare @dt1 datetime,@dt2 datetime
set @dt1='2010-12-11'
set @dt2='2010-12-15'
select top 1 convert(varchar,dateadd(dd,a.number,@dt1),23)
from # a
join (select distinct id from tb) b on 1=1
left join tb c on dateadd(dd,a.number,@dt1)=c.[date] and b.id=c.id
where dateadd(dd,a.number,@dt1)<=@dt2
and b.id=1
and c.id is null--drop table #,tb