1、使用橫向連接，以5天為例，簡單但不易擴展
with data as ( select * from yourtable where date>='2013-11-01' and date<'2013-12-01')
select distinct name
from data t1 join data t2 on t1.name=t2.name and t1.date=t2.date+1
join data t3 on t2.name=t3.name and t2.date=t3.date+1
join data t4 on t3.name=t4.name and t3.date=t4.date+1
join data t5 on t4.name=t5.name and t4.date=t5.date+1
2、使用縱向分組統計
with t1(id,rq) as (
select distinct 人員, date from 表 where date>='2013-11-01' and date<'2013-12-01' ),
--t1求出指定月的人員編號及不同的打卡日期
t2 as (select s2.* from t1 s1 join t1 s2 on s1.id=s2.id and s1.rq=s2.rq-1),
--t2求出所有上壹日也打過卡的日期
t3 as (select * from t1 except select * from t2),
--t3求出所有上壹日未打過卡的日期
t as (
select id,rq,1 days from t3
union all
select t1.id,t1.rq,t.days+1 from t1 join t on t1.id=t.id and t1.rq=t.rq+1
)
--t4遞歸調用，每連續壹日days+1，就是求每壹打卡時間是連續的第幾天
select id
from t
group by id
having max(days)>=5
order by id
以上就不刪了，以下可以改短點吧
with t as (
select 人員 id, date rq, 1 days from 表 t1
where not exists(select * from 表 t2 where t2.date=t1.date-1)
union all
select t1.id,t1.rq,t.days+1 from 表 t1 join t on t1.id=t.id and t1.rq=t.rq+1
)
select id
from t
group by id
having max(days)>=5
order by id