sql - 不使用 desc 显示 sql 中的最后 5 条记录

Question

我有一个这样的查询:

SELECT TOP 5
    CONVERT(varchar(15), CAST(Pout AS time), 100) AS 'OUT',
    CONVERT(varchar(15), CAST(pIn AS time), 100) AS 'IN',
    DATEDIFF(MINUTE, CONVERT(varchar(5), Pout, 108), CONVERT(varchar(5), pIn, 108)) AS [Total Used]
FROM 
    loginfo
WHERE 
    empid = 1001
    AND CONVERT(date, pDate) = '28/Nov/2016 2:45:00 PM'
    AND pOut IS NOT NULL
ORDER BY 
    pOut

这个查询工作正常，但我不想显示前 5 条记录，我需要显示最近 5 条记录，我的意思是最后 5 条记录，但我不能 ORDER BY pout DESC ，因为我想以正确的格式显示报告，但我只需要显示交易的最后 5 条记录，而不使用降序排列

例如，如果我有 10 条交易记录，我只想以正确的格式显示最后 5 条记录

Answer 1

改用ROW_NUMBER():

SELECT * FROM (
    SELECT
          CONVERT(varchar(15), CAST(Pout AS time), 100) AS [OUT],
          CONVERT(varchar(15), CAST(pIn AS time), 100) AS [IN],
          DATEDIFF(MINUTE, CONVERT(varchar(5), Pout, 108),     
          CONVERT(varchar(5), pIn, 108)) AS [Total Used],
          ROW_NUMBER() OVER(ORDER BY pOut DESC) as rnk
    FROM loginfo
    WHERE empid = 1001
      AND CONVERT(date, pDate) = '28/Nov/2016 2:45:00 PM'
      AND pOut IS NOT NULL) p
WHERE p.rnk <= 5
ORDER BY What_Ever_You_Want

或者，使用派生表:

SELECT * FROM (Your Current Query)
ORDER BY pOut ;