显示最近5个记录在SQL不使用递减

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 ; 

Answer 2

您可以使用ROW_NUMBER但也必须使用一个DESC它。

; WITH CTE AS 
(
    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 RN 
    FROM loginfo 
    WHERE empid = 1001 
    AND CONVERT(date, pDate) = '28/Nov/2016 2:45:00 PM' 
    AND pOut IS NOT NULL 
) SELECT * FROM CTE 
WHERE RN BETWEEN 1 AND 5 
ORDER BY OUT