PostgreSQL：在每组中选择前三名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27415706/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-10-21 01:42:33 来源:igfitidea点击:
PostgreSQL: select top three in each group
提问by zeoran
following is my sample table and rows
以下是我的示例表和行
create table com (company text,val int);
insert into com values ('com1',1),('com1',2),('com1',3),('com1',4),('com1',5);
insert into com values ('com2',11),('com2',22),('com2',33),('com2',44),('com2',55);
insert into com values ('com3',111),('com3',222),('com3',333),('com3',444),('com3',555);
I want to get the top 3 value of each company, expected output is :
我想获得每家公司的前 3 个价值，预期产出是：
company val
---------------
com1 5
com1 4
com1 3
com2 55
com2 44
com2 33
com3 555
com3 444
com3 333
回答by Veera
Try This:
试试这个：
SELECT company, val FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY
company order by val DESC) AS Row_ID FROM com
) AS A
WHERE Row_ID < 4 ORDER BY company
回答by poshest
Since v9.3 you can do a lateral join
从 v9.3 开始，您可以进行横向连接
select distinct com_outer.company, com_top.val from com com_outer
join lateral (
select * from com com_inner
where com_inner.company = com_outer.company
order by com_inner.val desc
limit 3
) com_top on true
order by com_outer.company;
It might be fasterbut, of course, you should test performance specifically on your data and use case.
它可能更快，但当然，您应该专门针对您的数据和用例测试性能。
