从查询结果中选择单行中的最后 N 行

Question

我想选择一列的最后 3 个结果（行），用";"分隔。例如我有一张桌子-->桌子

    ActivityID Ticketnumber Action             ActivityDate
    -----------------------------------------------------------------
    101         45678       abc                10/05/2015 10:00:40 AM
    102         45678       def                10/05/2015 10:05:40 AM
    103         45678       ghi                10/05/2015 10:02:40 AM
    104         45678       jkl                10/05/2015 11:03:40 AM
    105         45678       mno                10/05/2015 12:04:40 AM

在这里，我有不同的活动ID，对于每个工单号码，在不同的时间输入一个操作。现在，我需要为数据库中的所有票号选择最后三个操作。结果应如下所示：

    TicketNumber   Action
    ----------------------------------------------------
    45678          ghi; jkl; mno

注意：我需要表中所有票号的结果。对于某些工单，数据库中的操作可能少于三个，因此在这种情况下，只需要显示最后两个或最后一个操作。

非常感谢。

Answer 1

要获取最新的 3 条记录，请使用 ROW_NUMBER 。然后使用 FOR XML PATH('') 进行串联：

WITH Cte AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY TicketNumber ORDER BY ActivityID DESC)
    FROM tbl
)
SELECT *
FROM (
    SELECT DISTINCT TicketNumber 
    FROM tbl
) t
CROSS APPLY(
    SELECT STUFF((
        SELECT '; ' + Action
        FROM Cte
        WHERE 
            TicketNumber = t.TicketNumber
            AND rn <= 3
        ORDER BY rn DESC
        FOR XML PATH('')
    ), 1, 2, '') AS Action
) x 

Answer 2

这可能会执行得更快：

;WITH CTE as
(
  SELECT 
    ActivityID, 
    Ticketnumber,
    ActivityDate,
    Action,
    row_number() over (partition by ticketnumber order by activityid desc) rn 
  FROM yourtable
  -- if you only want one ticketnumber you add the next line
  -- WHERE TicketNumber = 45678
), Tickets as
(
  SELECT distinct TicketNumber 
  FROM yourtable
  -- if you only want one ticketnumber you add the next line
  -- WHERE TicketNumber = 45678
)
SELECT TicketNumber
    ,STUFF(( 
        SELECT '; ' + [Action] 
        FROM cte t1 
        WHERE t1.TicketNumber = t.TicketNumber
        and rn < 4
        ORDER BY [Action]
        for xml path(''), type 
    ).value('.', 'varchar(max)'), 1, 2, '') [values] 
FROM Tickets t