我需要按两列订购数据,我该怎么做？
这是我的表：
Name | ImpFile | ImpTime Sam Imp01 2012-05-16 09:54:02.477 Ann Imp01 2012-05-16 09:54:02.478 Mark Imp01 2012-05-16 09:54:02.477 John Import12 2012-05-16 09:55:37.384 Bart Import12 2012-05-16 09:55:37.387 Sasha Import12 2012-05-16 09:55:37.385
我需要通过ImpTime和ImpName对此表进行排序,它应该如下所示：
Name | ImpFile | ImpTime
Import12 2012-05-16 09:55:37.387
Bart Import12 2012-05-16 09:55:37.387
John Import12 2012-05-16 09:55:37.384
Sasha Import12 2012-05-16 09:55:37.385
Imp01 2012-05-16 09:54:02.478
Ann Imp01 2012-05-16 09:54:02.478
Mark Imp01 2012-05-16 09:54:02.477
Sam Imp01 2012-05-16 09:54:02.477
我正在使用此查询,但它不按名称对表进行排序,仅按时间顺序排序,当时间是多行的相同值时.
select Name,ImpFile,ImpTime from people union select distinct '',max(ImpTime) from people group by ImpFile order by ImpTime desc,Name
这个查询给我这样的表：
Name | ImpFile | ImpTime
Import12 2012-05-16 09:55:37.387
John Import12 2012-05-16 09:55:37.384
Bart Import12 2012-05-16 09:55:37.387
Sasha Import12 2012-05-16 09:55:37.385
Imp01 2012-05-16 09:54:02.478
Sam Imp01 2012-05-16 09:54:02.477
Ann Imp01 2012-05-16 09:54:02.478
Mark Imp01 2012-05-16 09:54:02.477
有没有办法同时按这两列排序？
编辑
当我使用ImpFile DESC,ImpTime desc的订单时会发生什么？
它给了我一个像这样的结果表：
Name | ImpFile | ImpTime
Import12 2012-05-16 09:55:37.387
Imp01 2012-05-16 09:54:02.478
Bart Import12 2012-05-16 09:55:37.387
John Import12 2012-05-16 09:55:37.384
Sasha Import12 2012-05-16 09:55:37.385
Ann Imp01 2012-05-16 09:54:02.478
Mark Imp01 2012-05-16 09:54:02.477
Sam Imp01 2012-05-16 09:54:02.477
解决方法
获取每个组的领导者并按降序时间对其进行排序：
with grp(Name,TimeGroup,ImpTime) as ( select cast(null as varchar(5)),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,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,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,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,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
