sql - 按多列排序

Question

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

这是我的 table :

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

Answer 1

为什么你可以这样做:

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

参见here举个例子

编辑

我有一个建议给你。也许是这样的:

测试数据

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