select *
from tb a
where exists (select 1 from (
select no,min(startTime)startTime,min(startHour)startHour,max(endTime )endTime ,max(endHour)endHour
from tb
group by no)b where a.no=b.no and a.starttime=b.startTime and a.startHour=b.startHour and a.endTime =b.endTime
and a.endHour=b.endHour)
from tb a
where exists (select 1 from (
select no,min(startTime)startTime,min(startHour)startHour,max(endTime )endTime ,max(endHour)endHour
from tb
group by no)b where a.no=b.no and a.starttime=b.startTime and a.startHour=b.startHour and a.endTime =b.endTime
and a.endHour=b.endHour)
1 1 2014-05-14 2014-05-14 09:00 17:20 001
2 1 2014-05-15 2014-05-15 08:00 18:20 001
3 1 2014-05-16 2014-05-16 08:00 17:20 001
4 1 2014-05-17 2014-05-17 08:00 15:20 001
5 2 2014-05-18 2014-05-18 08:00 15:20 002
6 2 2014-05-19 2014-05-19 08:00 17:20 002结果:No分组,取最小时间的startTime,startHour和最大小时的endTime ,endHour
ID No startTime endTime startHour endHour Person
1 1 2014-05-14 2014-05-17 09:00 15:20 001
2 2 2014-05-18 2014-05-19 08:00 17:20 002
create table wf
(ID tinyint,No int,startTime varchar(20),endTime varchar(20),startHour varchar(10),endHour varchar(10),Person varchar(10))insert into wf
select 1,1,'2014-05-14','2014-05-14','09:00','17:20','001' union all
select 2,1,'2014-05-15','2014-05-15','08:00','18:20','001' union all
select 3,1,'2014-05-16','2014-05-16','08:00','17:20','001' union all
select 4,1,'2014-05-17','2014-05-17','08:00','15:20','001' union all
select 5,2,'2014-05-18','2014-05-18','08:00','15:20','002' union all
select 6,2,'2014-05-19','2014-05-19','08:00','17:20','003'
select row_number() over(order by getdate()) 'ID',
[No],
max(case when srn=1 then startTime else '' end) 'startTime',
max(case when ern=1 then endTime else '' end) 'endTime',
max(case when srn=1 then startHour else '' end) 'startHour',
max(case when ern=1 then endHour else '' end) 'endHour',
max(case when srn=1 then Person else '' end) 'Person'
from (select *,
row_number() over(partition by [No] order by cast(startTime+' '+startHour+':00' as datetime)) 'srn',
row_number() over(partition by [No] order by cast(endTime+' '+endHour+':00' as datetime) desc) 'ern'
from wf) t
where srn=1 or ern=1
group by [No]/*
ID No startTime endTime startHour endHour Person
-------- ----------- -------------------- -------------------- ---------- ---------- ----------
1 1 2014-05-14 2014-05-17 09:00 15:20 001
2 2 2014-05-18 2014-05-19 08:00 17:20 002(2 row(s) affected)
*/