(Ms Access)Row_Number()Over分区
问题描述:
如何使用MS ACCESS上的分区转换row_number()函数? 我想实现的是:(Ms Access)Row_Number()Over分区
从这个表:
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
此输出,只显示前3最新每个ID:
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;
答
任何东西使用OVER
子句称为窗口函数。不幸的是,MS Access没有在这种情况下,开窗Functions.The最简单的解决方案的支持可能是回VBA代码:(
答
考虑计数相关子查询可以在任何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;
答
这可能是更简单,更快地使用前n个 - 你提到自己:
Select T.[ID], T.[EntryDate]
From Table1 As T
Where T.[EntryDate] In
(Select Top 3 S.[EntryDate]
From Table1 As S
Where S.[ID] = T.[ID]
Order By S.[EntryDate] Desc)
Order By T.[ID] Asc, T.[EntryDate] Asc
你有任何想法如何,我可以使用VBA代码来实现呢? – user1688194