declare @tb table (
[ID] int,
[badge] int,
[name] varchar(6),
[date] datetime
)
insert @tb
select 1,10066,'张立英','2011-08-01' union all
select 2,10066,'张立英','2011-08-02' union all
select 3,10066,'张立英','2011-08-03' union all
select 4,10066,'张立英','2011-08-04' union all
select 5,10066,'张立英','2011-08-05' union all
select 6,10066,'张立英','2011-08-06' union all
select 7,10066,'张立英','2011-08-07' union all
select 8,10066,'张立英','2011-08-08' union all
select 9,10066,'张立英','2011-08-09' union all
select 10,10066,'张立英','2011-08-10' union all
select 11,10070,'戴开忠','2011-08-07' union all
select 12,10070,'戴开忠','2011-08-13' union all
select 13,10070,'戴开忠','2011-08-14' union all
select 14,10070,'戴开忠','2011-08-15' union all
select 15,10070,'戴开忠','2011-08-16' union all
select 16,10070,'戴开忠','2011-08-17' union all
select 17,10070,'戴开忠','2011-08-18' union all
select 18,10075,'成定才','2011-08-06' union all
select 22,10109,'吴伦秀','2011-08-01' union all
select 24,10109,'吴伦秀','2011-08-13' union all
select 25,10109,'吴伦秀','2011-08-14' union all
select 26,10109,'吴伦秀','2011-08-16'


select a.*,b.date  from (
select row_number() over(order by t.badge)as rn, t.badge,t.name,date 
from @tb t
where not exists (select 1 from @tb where badge=t.badge and datediff(dd,t.date,date)=-1))as a
 join 
(
select row_number() over(order by t.badge)as rn, badge,date 
from @tb t
where not exists (select 1 from @tb where badge=t.badge and datediff(dd,t.date,date)=1))as b
on a.badge=b.badge and a.rn =b.rn 
/*
rn    badge    name    date    date
1    10066    张立英    2011-08-01 00:00:00.000    2011-08-10 00:00:00.000
2    10070    戴开忠    2011-08-07 00:00:00.000    2011-08-07 00:00:00.000
3    10070    戴开忠    2011-08-13 00:00:00.000    2011-08-18 00:00:00.000
4    10075    成定才    2011-08-06 00:00:00.000    2011-08-06 00:00:00.000
5    10109    吴伦秀    2011-08-01 00:00:00.000    2011-08-01 00:00:00.000
6    10109    吴伦秀    2011-08-13 00:00:00.000    2011-08-14 00:00:00.000
7    10109    吴伦秀    2011-08-16 00:00:00.000    2011-08-16 00:00:00.000

*/