select 工号 from 表 group by 工号 where 打卡时间 > 星期一那天 and 打卡时间 <= 星期天那天 having count(*) =7
--------------- 這樣不准的,打卡時間有上下班,只要一次沒打上的話,當然也不是連續了
那就having count(*) =14吧,呵呵
select 工号 from 表 where 打卡时间 > 固定时间 group by 工号 having count(*) <=7
--假设打卡时间为日期型 且一天打卡一次(不是则先处理为这样) --建立测试环境 create table 表 ( 工号 int not null, 打卡时间 datetime not null )insert into 表 select 1,'2007-05-02' union all select 1,'2007-05-03' union all select 1,'2007-05-04' union all select 1,'2007-05-05' union all select 1,'2007-05-06' union all select 1,'2007-05-07' union all select 1,'2007-05-08' union all select 1,'2007-05-13' union all select 1,'2007-05-14' union all select 1,'2007-05-15' union all select 1,'2007-05-16' union all select 1,'2007-05-17' union all select 1,'2007-05-18' union all select 1,'2007-05-19' union all select 2,'2007-05-02' union all select 2,'2007-05-03' union all select 2,'2007-05-04' union all select 2,'2007-05-05' union all select 2,'2007-05-06' union all select 2,'2007-05-07' union all select 2,'2007-05-08' union all select 2,'2007-05-12' union all select 2,'2007-05-13' union all select 31,'2007-05-02' union all select 31,'2007-05-03' union all select 31,'2007-05-05' union all select 31,'2007-05-06' union all select 31,'2007-05-07' union all select 31,'2007-05-08' union all select 31,'2007-05-09' GO --测试 select 工号,打卡时间,cast(0 as int) id, IDENTITY(int,1,1) id1 into # from 表update a set id=a.id1 from # a left join # b on a.工号=b.工号 and a.打卡时间+1=b.打卡时间 where b.工号 is null
select 工号,count(1) 连上7天的次数 from # a where id<>0 and(select count(1) from # where 工号=a.工号 and 打卡时间>=a.打卡时间 and 打卡时间<=a.打卡时间+7)>=7 group by 工号
having count(*) =7
select 工号 from 表 group by 工号 where 打卡时间 > 星期一那天 and 打卡时间 <= 星期天那天
having count(*) =7
---------------
這樣不准的,打卡時間有上下班,只要一次沒打上的話,當然也不是連續了
where 打卡时间 > 固定时间
group by 工号
having count(*) <=7
--建立测试环境
create table 表
(
工号 int not null,
打卡时间 datetime not null
)insert into 表
select 1,'2007-05-02'
union all
select 1,'2007-05-03'
union all
select 1,'2007-05-04'
union all
select 1,'2007-05-05'
union all
select 1,'2007-05-06'
union all
select 1,'2007-05-07'
union all
select 1,'2007-05-08'
union all
select 1,'2007-05-13'
union all
select 1,'2007-05-14'
union all
select 1,'2007-05-15'
union all
select 1,'2007-05-16'
union all
select 1,'2007-05-17'
union all
select 1,'2007-05-18'
union all
select 1,'2007-05-19'
union all
select 2,'2007-05-02'
union all
select 2,'2007-05-03'
union all
select 2,'2007-05-04'
union all
select 2,'2007-05-05'
union all
select 2,'2007-05-06'
union all
select 2,'2007-05-07'
union all
select 2,'2007-05-08'
union all
select 2,'2007-05-12'
union all
select 2,'2007-05-13'
union all
select 31,'2007-05-02'
union all
select 31,'2007-05-03'
union all
select 31,'2007-05-05'
union all
select 31,'2007-05-06'
union all
select 31,'2007-05-07'
union all
select 31,'2007-05-08'
union all
select 31,'2007-05-09'
GO
--测试
select 工号,打卡时间,cast(0 as int) id, IDENTITY(int,1,1) id1 into # from 表update a set id=a.id1 from # a left join # b on a.工号=b.工号 and a.打卡时间+1=b.打卡时间 where b.工号 is null
select 工号,count(1) 连上7天的次数 from # a
where id<>0 and(select count(1) from # where 工号=a.工号 and 打卡时间>=a.打卡时间 and 打卡时间<=a.打卡时间+7)>=7
group by 工号