postgreSQL-返回第n个出现的行

Question

我正在使用PostgreSQL，我应该过滤一个表，以获取每个作者的一本书的副本数量，以防(如果且仅当(这是作者的第三次发行。下面是一个示例表：

已发布标题<1>1

id	author_id	份数
1	1	2002	10
2	1	2000
3	2	2000
4		3	2001	1
5	1	2000	1
6	2	2010	10
7	3	2002	1
8	3	2002	10
9	2	2005	1

我想你想要row_number():

select t.*
from (select t.*,
row_number() over (partition by author_id order by published) as seqnum
from t
) t
where seqnum = 3;

您可以通过在over子句中对author_id进行分区并按author_id、published、id排序来使用密集秩

declare @tmp as table(id int, author_id int, published int, number_of_copies int)
insert into @tmp
values
(8, 3,  2002,   10),    
(9, 2,  2005,   1),
(1, 1,  2002,   10),    
(2, 1,  2000,   1), 
(3, 2,  2000,   1), 
(4, 3,  2001,   1), 
(5, 1,  2000,   1), 
(6, 2,  2010,   10),
(7, 3,  2002,   1)

select 
* 
from
(
select
Dense_Rank() over(partition by author_id  order by author_id,published, id) 
author_rank, * 
from @tmp
)x
where author_rank=3
order by author_id, published

输出

author_rank id  author_id   published   number_of_copies
3   1   1   2002    10
3   6   2   2010    10
3   8   3   2002    10