27,580
社区成员
发帖
与我相关
我的任务
分享
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
*/
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 t.badge,b.name,MIN(t.date)as begindate,MAX(b.date)as enddate from @tb t,@tb b where t.badge=b.badge
group by t.badge,b.name
/*
badge name begindate enddate
10066 张立英 2011-08-01 00:00:00.000 2011-08-10 00:00:00.000
10070 戴开忠 2011-08-07 00:00:00.000 2011-08-18 00:00:00.000
10075 成定才 2011-08-06 00:00:00.000 2011-08-06 00:00:00.000
10109 吴伦秀 2011-08-01 00:00:00.000 2011-08-16 00:00:00.000
*/
是要这种结果吗
select *
from tb t
where not exists (select 1 from tb where badge=t.badge and datediff(dd,t.date,date)=1)
/*******************
ID badge name date
----------- ----------- ------ -----------------------
10 10066 张立英 2011-08-10 00:00:00.000
11 10070 戴开忠 2011-08-07 00:00:00.000
17 10070 戴开忠 2011-08-18 00:00:00.000
18 10075 成定才 2011-08-06 00:00:00.000
22 10109 吴伦秀 2011-08-01 00:00:00.000
25 10109 吴伦秀 2011-08-14 00:00:00.000
26 10109 吴伦秀 2011-08-16 00:00:00.000
(7 行受影响)
--> 测试数据:[tb]
go
if object_id('[tb]') is not null
drop table [tb]
go
create table [tb](
[ID] int,
[badge] int,
[name] varchar(6),
[date] datetime
)
go
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 * from tb a
where [date]=(select max([date]) from tb b
where a.badge =b.badge )
union all
select ID,[badge],[name],[date] from tb a
where a.[date] not in
(select dateadd(dd,1,[date]) from tb b where a.[badge]=b.[badge])
and a.[date] not in (select dateadd(dd,-1,[date]) from tb b
where a.[badge]=b.[badge])
order by badge
/*
ID badge name date
26 10109 吴伦秀 2011-08-16 00:00:00.000
18 10075 成定才 2011-08-06 00:00:00.000
17 10070 戴开忠 2011-08-18 00:00:00.000
10 10066 张立英 2011-08-10 00:00:00.000
11 10070 戴开忠 2011-08-07 00:00:00.000
18 10075 成定才 2011-08-06 00:00:00.000
22 10109 吴伦秀 2011-08-01 00:00:00.000
26 10109 吴伦秀 2011-08-16 00:00:00.000
*/