获取每个ID的最新状态和日期

Question

在Server 2008上工作，我一直在为如何查询这个问题而苦苦挣扎。假设我有下表：

ID      Status      Date (YYYY-MM-DD)
A-1     Open        2018-01-01
A-1     Closed      2018-01-05
B-2     Closed      2018-02-01
B-2     Open        2018-02-06
B-2     Closed      2018-02-06
C-3     Closed      2018-03-01
C-3     Open        2018-03-03
C-3     Closed      2018-03-05
C-3     Open        2018-03-07

我希望结果是一个由ID以及与该ID的最新活动相对应的状态和日期组成的表。如果在同一天同时存在打开状态和关闭状态，则应该默认为已关闭状态。因此，使用上表，结果应该是：

ID      Status      Date (YYYY-MM-DD)
A-1     Closed      2018-01-05
B-2     Closed      2018-02-06
C-3     Open        2018-03-07

Answer 1

WITH cte AS
 (
   SELECT *
     ,ROW_NUMBER()
      OVER(PARTITION BY ID  -- latest date per ID, prefer 'Closed' over 'Open'
           ORDER BY Date DESC, Status) AS rn
   FROM tab
 )
SELECT * -- all columns but rn
FROM cte
WHERE rn = 1;

Answer 2

你可以用ROW_NUMBER

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date DESC) AS rn
      FROM tab) sub
WHERE sub.rn = 1;

Answer 3

在Server中，可以使用row_number()。然而，最有效的方法往往是比较老套的方法：

select t.*
from t
where t.date = (select max(t2.date) from t t2 where t2.id = t.id);

如果您在t(id, date)上有一个索引，这一点尤其正确。

另一个可以具有更好性能的方法是apply。这假设您有一个ids表

select t.*
from ids cross apply
     (select top 1 t.*
      from t
      where t.id = ids.id
      order by t.date desc
     ) t;