按多列排序

小编典典

按多列排序

sql

我需要按两列排序数据，我该怎么做？

这是我的表：

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 '', ImpFile, 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

有什么办法可以同时通过这两列进行订购吗？

编辑当我使用时会发生什么order by 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

阅读 162

2021-05-16

共1个答案

小编典典

获取每个小组的负责人，并按降序对他们进行排序：

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

2021-05-16