11,849
社区成员
发帖
与我相关
我的任务
分享
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (工号 int,日期 datetime)
insert into [TB]
select 10729,'2011-2-1' union all
select 10729,'2011-2-2' union all
select 10729,'2011-2-3' union all
select 10729,'2011-2-4' union all
select 10729,'2011-2-5' union all
select 20321,'2011-2-8'
select * from [TB]
;with TT
as(
select 工号,日期,datediff(dd,ROW_NUMBER() over (partition by 工号 order by 日期),日期) as nn
from TB ), --要除去对应公众假期的话,再这里面处理一下即可
T1
as(
select *,ROW_NUMBER () over(partition by 工号,nn order by getdate()) as N from TT )
select 工号 from T1 group by 工号 having COUNT(1) >=5 --如果是要大于7天就修改成 >7
/*10729