使用row_number函数查询连续7天出勤的员工

/***************************************************

          作者：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)

 

相类似的问题，也可以去引申