相连日期组合为一条记录，不连的日期单独一条
请假信息：
---表结构 tb
ID badge name date
110066张立英2011-08-01
210066张立英2011-08-02
310066张立英2011-08-03
410066张立英2011-08-04
510066张立英2011-08-05
610066张立英2011-08-06
710066张立英2011-08-07
810066张立英2011-08-08
910066张立英2011-08-09
1010066张立英2011-08-10
1110070戴开忠2011-08-07
1210070戴开忠2011-08-13
1310070戴开忠2011-08-14
1410070戴开忠2011-08-15
1510070戴开忠2011-08-16
1610070戴开忠2011-08-17
1710070戴开忠2011-08-18
1810075成定才2011-08-06
2210109吴伦秀2011-08-01
2410109吴伦秀2011-08-13
2510109吴伦秀2011-08-14
2610109吴伦秀2011-08-16
想要结果：
badge name begindate enddate
10066张立英 2011-08-01 2011-08-10
10070戴开忠 2011-08-07 2011-08-07
10070戴开忠 2011-08-13 2011-08-18
10075成定才 2011-08-06 2011-08-06
10109吴伦秀 2011-08-01 2011-08-01
10109吴伦秀 2011-08-13 2011-08-14
10109吴伦秀 2011-08-16 2011-08-16
在线等各位大侠
[解决办法]
declare @tb table ([ID] int,[badge] int,[name] varchar(6),[date] datetime)insert @tbselect 1,10066,'张立英','2011-08-01' union allselect 2,10066,'张立英','2011-08-02' union allselect 3,10066,'张立英','2011-08-03' union allselect 4,10066,'张立英','2011-08-04' union allselect 5,10066,'张立英','2011-08-05' union allselect 6,10066,'张立英','2011-08-06' union allselect 7,10066,'张立英','2011-08-07' union allselect 8,10066,'张立英','2011-08-08' union allselect 9,10066,'张立英','2011-08-09' union allselect 10,10066,'张立英','2011-08-10' union allselect 11,10070,'戴开忠','2011-08-07' union allselect 12,10070,'戴开忠','2011-08-13' union allselect 13,10070,'戴开忠','2011-08-14' union allselect 14,10070,'戴开忠','2011-08-15' union allselect 15,10070,'戴开忠','2011-08-16' union allselect 16,10070,'戴开忠','2011-08-17' union allselect 17,10070,'戴开忠','2011-08-18' union allselect 18,10075,'成定才','2011-08-06' union allselect 22,10109,'吴伦秀','2011-08-01' union allselect 24,10109,'吴伦秀','2011-08-13' union allselect 25,10109,'吴伦秀','2011-08-14' union allselect 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 twhere 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 twhere not exists (select 1 from @tb where badge=t.badge and datediff(dd,t.date,date)=1))as bon a.badge=b.badge and a.rn =b.rn /*rn badge name date date1 10066 张立英 2011-08-01 00:00:00.000 2011-08-10 00:00:00.0002 10070 戴开忠 2011-08-07 00:00:00.000 2011-08-07 00:00:00.0003 10070 戴开忠 2011-08-13 00:00:00.000 2011-08-18 00:00:00.0004 10075 成定才 2011-08-06 00:00:00.000 2011-08-06 00:00:00.0005 10109 吴伦秀 2011-08-01 00:00:00.000 2011-08-01 00:00:00.0006 10109 吴伦秀 2011-08-13 00:00:00.000 2011-08-14 00:00:00.0007 10109 吴伦秀 2011-08-16 00:00:00.000 2011-08-16 00:00:00.000*/
