logo标签列表

按多列排序

sqlsql-serversql-server-2005
9

我需要按照两列数据进行排序，应该如何操作？

这是我的表格：

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
3
有没有办法同时按这两列排序？你已经在同时按照两列排序了，那么你的问题是什么？时间优先，因为你想首先按照第一列排序。只有在具有相同 "ImpTime" 的记录中才会考虑“名称”。 - Tim Schmelter
@TimSchmelter 有没有方法将这两列的优先级设置为相同？ - Brezhnews
根据您所期望的输出，无论我如何看待它，毫秒在排序时都被忽略了。如果是这种情况，我的答案是正确的。除非您能提供另一个数据样本，并证明其与期望的输出不同。 ツ - Michael Buen
如果您按照降序排列ImpTime并保留毫秒，以期望的输出方式进行排序，为什么John（2012-05-16 09:55:37. 384）会在Sasha（2012-05-16 09:55:37. 385）之前？ - Michael Buen
1
当以日期时间格式存储时，“2012-05-16 09:55:37.385”会变成“2012-05-16 09:55:37.384”。嗯...为什么你在问题中没有提到这个特殊情况呢？如果它们都被存储为384，那么StackOverflow怎么知道我们正在查询正确的数据呢？如果数据一致性得到维护（385被存储为385，384被存储为384），那么你的查询已经是正确的了；然而你并没有快速地提到数据的特殊性：日期时间格式会丢失385的精度，所以它实际上被存储为384。 - Michael Buen
显示剩余4条评论
6个回答
6
为什么你不能像这样做呢：
order by ImpFile DESC, ImpTime desc

不，它不会导致你所展示的结果。它会导致这个结果：
        Import12    2012-05-16 09:55:37.387
Bart    Import12    2012-05-16 09:55:37.387
Sasha   Import12    2012-05-16 09:55:37.387
John    Import12    2012-05-16 09:55:37.383
        Imp01       2012-05-16 09:54:02.477
Ann     Imp01       2012-05-16 09:54:02.477
Mark    Imp01       2012-05-16 09:54:02.477
Sam     Imp01       2012-05-16 09:54:02.477

请参见这里的示例。

编辑

我有一个建议。也许可以像这样：

测试数据

DECLARE @T TABLE(Name VARCHAR(100),ImpFile VARCHAR(100),ImpTime DATETIME)

INSERT INTO @T
    ([Name], [ImpFile], [ImpTime])
VALUES
    ('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');

查询

;WITH CTE
AS
(   
    SELECT
        ROW_Number() OVER(PARTITION BY  t.[ImpFile] 
                     ORDER BY t.[ImpTime] DESC) AS RowNbr,
        '' AS Name,
        t.ImpFile,
        t.[ImpTime]
    FROM
        @T AS t
)
SELECT
    CTE.Name,
    CTE.ImpFile,
    CTE.[ImpTime],
    0 as SortOrder
FROM
    CTE
WHERE
    CTE.RowNbr=1
UNION ALL
SELECT
    t.Name,
    t.ImpFile,
    t.[ImpTime],
    1 as SortOrder
FROM
    @T AS t
ORDER BY
    ImpFile DESC,SortOrder, ImpTime desc
这只是我的表的一部分，也许这些可以得到像这样的结果，但有些记录的结果就是我所展示的。 - Brezhnews
如果您不保证这个特定的顺序，那就不能确保。您必须在ORDER BY中显式指定每个汇总行应该放在其各自组的开头。楼主是正确的，在您的示例输出中仅偶然出现了所需的结果。对于更大的数据集，结果可能不同。 - Andriy M
好的。已更新答案，请查看。 - Arion
这应该更接近了，但仍不能保证摘要行（名称为空的行）会在其相应的详细行之前。你可能会得到(空名称)，然后是Bart，或者反过来。我认为您可能需要一个额外的列来显示哪些行是详细信息，哪些是摘要（并在排序中使用该列）。 - Andriy M
我明白你的意思。我已经根据你的建议更新了答案。感谢你指出这一点。你几乎要抓住我了 :P。为什么要踩我？你对那个解决方案满意吗？ - Arion
显示剩余4条评论
5
获取每个组的领导者，并按时间降序排序：
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上放一个CASE WHEN。
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

Live test: http://www.sqlfiddle.com/#!3/c7859/25

不错。我也在考虑类似的东西，但不知道具体该怎么做，所以谢谢你啊。 :) - Brezhnews
2
首先，您应该意识到对于 datetime 类型的粒度，SQL Server 无法区分 2012-05-16 09:55:37.384 和 2012-05-16 09:55:37.385：两者都将存储为 2012-05-16 09:55:37.384。
考虑到这一点，并假设您想按照 MAX(ImpTime) DESC 对组进行排序以及按照 ImpTime DESC 对详细行进行排序，您可以尝试以下操作：
WITH agg AS (
  SELECT
    *,
    ImpTimeMax = MAX(ImpTime) OVER (PARTITION BY ImpFile),
    rn         = ROW_NUMBER() OVER (PARTITION BY ImpFile ORDER BY ImpTime DESC)
  FROM Table1
)
SELECT
  Name = CASE x.IsAgg WHEN 1 THEN '' ELSE agg.Name END,
  agg.ImpFile,
  agg.ImpTime
FROM agg
  INNER JOIN (SELECT 0 UNION ALL SELECT 1) x (IsAgg) ON x.IsAgg = 0 OR agg.rn = 1
ORDER BY
  agg.ImpTimeMax DESC,  /* the primary order for groups */
  agg.ImpFile    ASC ,  /* in case two or more groups have the same max time */
  x.IsAgg        DESC,  /* the group summary row goes first */
  agg.ImpTime    DESC,  /* or: agg.rn ASC */
  agg.Name       ASC    /* in case two or more people have the same time */

当运行 在 SQL Fiddle 上 时，会为您的示例生成以下输出：
NAME   IMPFILE   IMPTIME
-----  --------  -----------------------
       Import12  2012-05-16 09:55:37.387
Bart   Import12  2012-05-16 09:55:37.387
Sasha  Import12  2012-05-16 09:55:37.385
John   Import12  2012-05-16 09:55:37.384
       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

请注意，我暂时将 ImpTime 定义为 varchar，而不是 datetime，仅出于更好的演示目的，因为如我所说，datetime 的粒度会导致存储的值略有不同（相应地，产生稍微不同的输出）。
1

您可能想要清除毫秒：http://www.sqlfiddle.com/#!3/35065/2

select Name, ImpFile, 

   ImpTimeX = 
      DATEADD(ms, -DATEPART(ms, ImpTime), ImpTime)

from tbl

union

select distinct '', ImpFile, 

   ImpTimeX = 
       MAX(DATEADD(ms, -DATEPART(ms, ImpTime), ImpTime))

from tbl 
group by ImpFile
order by ImpTimeX desc, Name

输出：

NAME           IMPFILE        IMPTIMEX
               Import12       May, 16 2012 09:55:37-0700
Bart           Import12       May, 16 2012 09:55:37-0700
John           Import12       May, 16 2012 09:55:37-0700
Sasha          Import12       May, 16 2012 09:55:37-0700
               Imp01          May, 16 2012 09:54:02-0700
Ann            Imp01          May, 16 2012 09:54:02-0700
Mark           Imp01          May, 16 2012 09:54:02-0700
Sam            Imp01          May, 16 2012 09:54:02-0700

在此处清除毫秒的技术来源：SQL Server从日期时间中删除毫秒

如果您想保留并显示原始时间，只需执行以下操作：http://www.sqlfiddle.com/#!3/35065/1。
with a as(

  select Name, ImpFile, 

     ImpTimeX = 
        DATEADD(ms, -DATEPART(ms, ImpTime), ImpTime),

     ImpTime

  from tbl

  union

  select distinct '', ImpFile, 

     ImpTimeX = 
         MAX(DATEADD(ms, -DATEPART(ms, ImpTime), ImpTime)),

     MAX(ImpTime)

  from tbl 
  group by ImpFile

)
select Name, ImpFile, ImpTime
from a
order by ImpTimeX desc, Name

我认为SqlFiddle不显示毫秒，请在你的Sql Server上尝试第二个查询，在其他操作系统上，我目前无法看到第二个查询的实际输出。

这是第二个查询的输出结果，包含日期时间信息，并在SSMS上进行了测试：
Name    ImpFile         ImpTime     
        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
        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可能会有+/- 2秒的情况。在这种情况下，顺序只部分正确。 - Brezhnews
1
看一下我的输出，它符合你的期望输出。在这里也可以检查实时测试：http://www.sqlfiddle.com/#!3/35065/1 - Michael Buen
在http://www.sqlfiddle.com/#!3/35065/1中，当ImpFile为“Import12”时，John应该排在Sasha之前，因为字母“J”在“S”之前。 - Brezhnews
但是您的要求是按时间降序排序，是吗？那么约翰怎么能排在莎莎之前呢？看看您的数据：巴特（.387），约翰（.384），莎莎（.385）。如果我们按时间降序排序：巴特（.387），莎莎（.385），约翰（.384）。如果您的第一个要求是按时间降序排序（然后按名称排序），那么约翰怎么可能比莎莎先呢？ - Michael Buen
那就是关键点了，我需要让仅获取ImpFile和ImpTime的第一个查询按照ImpTime降序排序，但同时获取Names的查询按照Name排序。或许有一种方法可以做到这一点？可以对每个union查询单独排序。 - Brezhnews
0

看起来你的union查询中，order by只被应用于第二个select。

尝试使用subselect创建一个临时表来应用order by：

select Name, ImpFile, ImpTime from (
select Name, ImpFile, ImpTime from people
union
select distinct '', ImpFile, max(ImpTime) from people group by ImpFile
) order by ImpTime desc, Name
我用手机回答了你的问题；等我到电脑前再验证一下，应该可以正常工作。 - Go Dan
这与 OP 的查询相同。在 UNION 上使用 ORDER BY 适用于整个查询，而不仅仅是最后一个查询。因此，您查询中的括号是多余的。 - Michael Buen
1
看这两个查询：http://www.sqlfiddle.com/#!3/22aeb/2 它们的行为完全相同。 - Michael Buen
0

您可以通过按ImpFile desc，Name排序来获得所需的输出

select Name, ImpFile, ImpTime
    from dbo.tbl_stack 
    union
    select distinct '', ImpFile, max(ImpTime)
    from dbo.tbl_stack 
    group by ImpFile
    order by ImpFile desc,Name

这是输出
Name    ImpFile         ImpTime 
        Import12        2012-05-16 09:55:37.387
Bar     Import12        2012-05-16 09:55:37.387
John    Import12        2012-05-16 09:55:37.383
Sasha   Import12        2012-05-16 09:55:37.387
        Imp01       2012-05-16 09:54:02.477
Ann     Imp01       2012-05-16 09:54:02.477
Mark    Imp01       2012-05-16 09:54:02.477
Sam     Imp01           2012-05-16 09:54:02.477
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接