sql - (MS Access) Row_Number() 超过分区

Question

如何在 MS ACCESS 上转换带有过度分区的 row_number() 函数？ 我想要实现的是:

来自这张表:

ID  | EntryDate  
10  | 2016-10-10
10  | 2016-12-10
10  | 2016-12-31
10  | 2017-01-31
10  | 2017-03-31
11  | 2015-01-31
11  | 2017-01-31

对于此输出，仅显示每个 ID 的前 3 个最新:

ID  | EntryDate  
10  | 2016-12-31
10  | 2017-01-31
10  | 2017-03-31
11  | 2015-01-31
11  | 2017-01-31

在 SQL Server 上，我可以使用以下代码实现此目的:

select T.[ID],
   T.[AptEndDate],
from (
 select T.[ID],
        T.[AptEndDate],
        row_number() over(partition by T.[ID] order by T.[AptEndDate] desc) as rn
 from Table1 as T
 ) as T
where T.rn <= 3;

Answer 1

考虑一个可以在任何 RDBMS 中工作的计数相关子查询。

select T.[ID], T.[EntryDate]
from 
 (select sub.[ID],
         sub.[EntryDate],
         (select count(*) from Table1 c
          where c.ID = sub.ID 
          and c.[EntryDate] >= sub.[EntryDate]) as rn
 from Table1 as sub
 ) as T
where T.rn <= 3;