获取每个小组的负责人，并按降序对他们进行排序：
with grp(Name,ImpFile,TimeGroup,ImpTime) as
(
select cast(null as varchar(5)), ImpFile, max(ImpTime) as TimeGroup,
max(ImpTime) as ImpTime
from people
group by ImpFile
)
select *
from grp
order by TimeGroup desc;
输出：
NAME IMPFILE TIMEGROUP IMPTIME
(null) Import12 2012-05-16 09:55:37.3870000 2012-05-16 09:55:37.3870000
(null) Imp01 2012-05-16 09:54:02.4780000 2012-05-16 09:54:02.4780000
然后将跟随者加入领导者并获取领导者的时间（TimeGroup）：
with grp(Name,ImpFile,TimeGroup,ImpTime) as
(
select cast(null as varchar(5)), ImpFile, max(ImpTime) as TimeGroup,
max(ImpTime) as ImpTime
from people
group by ImpFile
union all
select p.Name, p.ImpFile, ldr.TimeGroup, p.ImpTime
from people p
inner join grp ldr -- leader
on ldr.name is null and ldr.ImpFile = p.ImpFile
)
select Name, ImpFile, ImpTime
from grp
order by TimeGroup desc, Name
输出：
NAME IMPFILE IMPTIME
(null) Import12 2012-05-16 09:55:37.3870000
Bart Import12 2012-05-16 09:55:37.3870000
John Import12 2012-05-16 09:55:37.3840000
Sasha Import12 2012-05-16 09:55:37.3850000
(null) Imp01 2012-05-16 09:54:02.4780000
Ann Imp01 2012-05-16 09:54:02.4780000
Mark Imp01 2012-05-16 09:54:02.4770000
Sam Imp01 2012-05-16 09:54:02.4770000
查询的逻辑是，我们根据ImpFile将跟随者（带名称的人）的时间与他们的领导者的时间（TimeGroup）进行对齐。领导者及其追随者具有相同的时间组，因此，当我们按时间对其进行排序时，它们将相互依存；然后在那之后，我们按名称排序
实时测试：http：//www.sqlfiddle.com/#！3 /c7859 / 21
如果我们希望组长出现在其关注者之后，只需在ORDER BY上放一个案子：
with grp(Name,ImpFile,TimeGroup,ImpTime) as
(
select cast(null as varchar(5)), ImpFile, max(ImpTime) as TimeGroup,
max(ImpTime) as ImpTime
from people
group by ImpFile
union all
select p.Name, p.ImpFile, ldr.TimeGroup, p.ImpTime
from people p
inner join grp ldr -- leader
on ldr.name is null and ldr.ImpFile = p.ImpFile
)
select Name, ImpFile, ImpTime
from grp
order by TimeGroup desc,
case
when Name is null then 2 -- leader last
else 1 -- followers first
end,
Name
输出：
NAME IMPFILE IMPTIME
Bart Import12 2012-05-16 09:55:37.3870000
John Import12 2012-05-16 09:55:37.3840000
Sasha Import12 2012-05-16 09:55:37.3850000
(null) Import12 2012-05-16 09:55:37.3870000
Ann Imp01 2012-05-16 09:54:02.4780000
Mark Imp01 2012-05-16 09:54:02.4770000
Sam Imp01 2012-05-16 09:54:02.4770000
(null) Imp01 2012-05-16 09:54:02.4780000
实时测试：http：//www.sqlfiddle.com/#！3 / c7859 / 23
怎么运行的：
with grp(Name,ImpFile,TimeGroup,ImpTime) as
(
select cast(null as varchar(5)), ImpFile, max(ImpTime) as TimeGroup,
max(ImpTime) as ImpTime
from people
group by ImpFile
union all
select p.Name, p.ImpFile, ldr.TimeGroup, p.ImpTime
from people p
inner join grp ldr -- leader
on ldr.name is null and ldr.ImpFile = p.ImpFile
)
select *
from grp
order by TimeGroup desc, Name;
输出：
NAME IMPFILE IMPTIME TIMEGROUP
(null) Import12 2012-05-16 09:55:37.3870000 2012-05-16 09:55:37.3870000
Bart Import12 2012-05-16 09:55:37.3870000 2012-05-16 09:55:37.3870000
John Import12 2012-05-16 09:55:37.3840000 2012-05-16 09:55:37.3870000
Sasha Import12 2012-05-16 09:55:37.3850000 2012-05-16 09:55:37.3870000
(null) Imp01 2012-05-16 09:54:02.4780000 2012-05-16 09:54:02.4780000
Ann Imp01 2012-05-16 09:54:02.4780000 2012-05-16 09:54:02.4780000
Mark Imp01 2012-05-16 09:54:02.4770000 2012-05-16 09:54:02.4780000
Sam Imp01 2012-05-16 09:54:02.4770000 2012-05-16 09:54:02.4780000
实时测试：http：//www.sqlfiddle.com/#！3 / c7859 / 25
602392714
清零编程群