sql – 按多列排序

发布时间：2020-12-12 06:28:22 所属栏目：MsSql教程来源：网络整理

导读：我需要按两列订购数据,我该怎么做？这是我的表： Name | ImpFile | ImpTimeSam Imp01 2012-05-16 09:54:02.477Ann Imp01 2012-05-16 09:54:02.478Mark Imp01 2012-05-16 09:54:02.477John Import12 2012-05-16 09:55:37.384Bart Import12 2012-05-16 09:55:3

我需要按两列订购数据,我该怎么做？

这是我的表：

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

现场测试：http://www.sqlfiddle.com/#!3/c7859/25

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!