/***************************************************
作者:herowang(让你望见影子的墙)
日期:2009.11.14
注: 转载请保留此信息
更多内容,请访问我的博客:blog.csdn.net/herowang
****************************************************/
declare @a table (d datetime,id int,flag bit)
insert @a select '2004-01-01',1,1
union all select '2004-01-02',1,1
union all select '2004-01-03',1,1
union all select '2004-01-04',1,1
union all select '2004-01-05',1,1
union all select '2004-01-06',1,1
union all select '2004-01-07',1,1
union all select '2004-01-08',1,1
union all select '2004-01-09',1,1
union all select '2004-01-01',2,1
union all select '2004-01-02',2,1
union all select '2004-01-03',2,1
union all select '2004-01-04',2,1
union all select '2004-01-05',2,1
union all select '2004-01-06',2,1
union all select '2004-07-08',2,1
union all select '2004-01-09',2,1
union all select '2004-01-10',2,1
查询连续7天初期的员工,那么必须满足一下条件:
下面的7行,日期必须是连续的,那么在第七行的日期为第一天的日期加7,行数也是加7
;with
wang as (select row=row_number() over (partition by id order by d),* from @a)
select distinct id from wang t
where exists(select 1 from wang where d=t.d+7 and row=t.row+7 and id=t.id)
相类似的问题,也可以去引申