select * from 表 a where exists(select 1 from 表 b where a.工號=b.工號 and cast(b.年+'-'+b.月+'-'+b.日 as datetime)=cast(a.年+'-'+a.月+'-'+a.日 as datetime) and exists(select 1 from 表 b where a.工號=b.工號 and cast(b.年+'-'+b.月+'-'+b.日 as datetime)+1=cast(a.年+'-'+a.月+'-'+a.日 as datetime) and exists(select 1 from 表 b where a.工號=b.工號 and cast(b.年+'-'+b.月+'-'+b.日 as datetime)+2=cast(a.年+'-'+a.月+'-'+a.日 as datetime) and exists(select 1 from 表 b where a.工號=b.工號 and cast(b.年+'-'+b.月+'-'+b.日 as datetime)+3=cast(a.年+'-'+a.月+'-'+a.日 as datetime) and exists(select 1 from 表 b where a.工號=b.工號 and cast(b.年+'-'+b.月+'-'+b.日 as datetime)+4=cast(a.年+'-'+a.月+'-'+a.日 as datetime) and exists(select 1 from 表 b where a.工號=b.工號 and cast(b.年+'-'+b.月+'-'+b.日 as datetime)+5=cast(a.年+'-'+a.月+'-'+a.日 as datetime) and exists(select 1 from 表 b where a.工號=b.工號 and cast(b.年+'-'+b.月+'-'+b.日 as datetime)+6=cast(a.年+'-'+a.月+'-'+a.日 as datetime)
create table 你的表(年 int,月 int,日 int,工號 int)insert 你的表 values(2003,11,4,14743) insert 你的表 values(2003,11,5,14743) insert 你的表 values(2003,11,6,14743) insert 你的表 values(2003,11,7,14743) insert 你的表 values(2003,11,8,14743) insert 你的表 values(2003,11,9,14743) insert 你的表 values(2003,11,10,14743) insert 你的表 values(2003,11,14,14743)declare @a int,@b datetime,@c int set @a=0 select *,0 flag into #b from 你的表 order by 工號,年,月,日 update #b set @a=case when 工號=@c and datediff(day,@b,cast(年 as char(4))+right(100+月,2)+right(100+日,2))=1 then @a else @a+1 end, @b=cast(年 as char(4))+right(100+月,2)+right(100+日,2),@c=工號,flag=@a select 年,月,日,工號 from #b where flag in (select flag from #b group by flag having sum(1)>=7)go drop table 你的表,#b
where
exists(select 1 from 表 b where a.工號=b.工號
and
cast(b.年+'-'+b.月+'-'+b.日 as datetime)=cast(a.年+'-'+a.月+'-'+a.日 as datetime)
and
exists(select 1 from 表 b where a.工號=b.工號
and
cast(b.年+'-'+b.月+'-'+b.日 as datetime)+1=cast(a.年+'-'+a.月+'-'+a.日 as datetime)
and
exists(select 1 from 表 b where a.工號=b.工號
and
cast(b.年+'-'+b.月+'-'+b.日 as datetime)+2=cast(a.年+'-'+a.月+'-'+a.日 as datetime)
and
exists(select 1 from 表 b where a.工號=b.工號
and
cast(b.年+'-'+b.月+'-'+b.日 as datetime)+3=cast(a.年+'-'+a.月+'-'+a.日 as datetime)
and
exists(select 1 from 表 b where a.工號=b.工號
and
cast(b.年+'-'+b.月+'-'+b.日 as datetime)+4=cast(a.年+'-'+a.月+'-'+a.日 as datetime)
and
exists(select 1 from 表 b where a.工號=b.工號
and
cast(b.年+'-'+b.月+'-'+b.日 as datetime)+5=cast(a.年+'-'+a.月+'-'+a.日 as datetime)
and
exists(select 1 from 表 b where a.工號=b.工號
and
cast(b.年+'-'+b.月+'-'+b.日 as datetime)+6=cast(a.年+'-'+a.月+'-'+a.日 as datetime)
insert 你的表 values(2003,11,5,14743)
insert 你的表 values(2003,11,6,14743)
insert 你的表 values(2003,11,7,14743)
insert 你的表 values(2003,11,8,14743)
insert 你的表 values(2003,11,9,14743)
insert 你的表 values(2003,11,10,14743)
insert 你的表 values(2003,11,14,14743)declare @a int,@b datetime,@c int
set @a=0
select *,0 flag into #b from 你的表 order by 工號,年,月,日
update #b set @a=case when 工號=@c and datediff(day,@b,cast(年 as char(4))+right(100+月,2)+right(100+日,2))=1 then @a else @a+1 end,
@b=cast(年 as char(4))+right(100+月,2)+right(100+日,2),@c=工號,flag=@a
select 年,月,日,工號 from #b where flag in (select flag from #b group by flag having sum(1)>=7)go
drop table 你的表,#b