PostgreSQL 相当于 TOP n WITH TIES: LIMIT "with ties"？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9629953/
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
PostgreSQL equivalent for TOP n WITH TIES: LIMIT "with ties"?
提问by Andrew
I'm looking for something similar this in SQL Server:
我正在 SQL Server 中寻找类似的东西：
SELECT TOP n WITH TIES FROM tablename
I know about LIMITin PostgreSQL, but does the equivalent of the above exist? I'm just curious as it would save an extra query each time for me.
我知道LIMIT在 PostgreSQL 中，但是否存在上述等效项？我只是好奇，因为它每次都会为我节省一个额外的查询。
If I have a table Numberswith attribute nums: {10, 9, 8, 8, 2}. I want to do something like:
如果我有一个Numbers带有属性的表nums: {10, 9, 8, 8, 2}。我想做类似的事情：
SELECT nums FROM Numbers ORDER BY nums DESC LIMIT *with ties* 3
It should return {10, 9, 8, 8}because it takes the top 3 plus the extra 8since it ties the other one.
它应该返回，{10, 9, 8, 8}因为它需要前 3 个加上额外的，8因为它与另一个联系在一起。
回答by Erwin Brandstetter
There is no WITH TIESclause in PostgreSQLlike there is in SQL Server.
In PostgreSQL I would substitute this for TOP n WITH TIES .. ORDER BY <something>:
PostgreSQL 中没有像SQL Server 中那样的WITH TIES子句。
在 PostgreSQL 中，我会将其替换为：TOP n WITH TIES .. ORDER BY <something>
WITH cte AS (
SELECT *, rank() OVER (ORDER BY <something>) AS rnk
FROM tbl
)
SELECT *
FROM cte
WHERE rnk <= n;
To be clear, rank()is right, dense_rank()would be wrong (return too many rows).
Consider this quote from the SQL Server docs (from the link above):
要清楚，rank()是对的，dense_rank()会是错的（返回太多行）。
考虑 SQL Server 文档中的引用（来自上面的链接）：
For example, if expression is set to 5 but 2 additional rows match the values of the ORDER BY columns in row 5, the result set will contain 7 rows.
例如，如果表达式设置为 5，但另外 2 行与第 5 行中 ORDER BY 列的值匹配，则结果集将包含 7 行。
The job of WITH TIESis to include all peers of the last row in the top nas defined by the ORDER BYclause. rank()gives the exact same result.
的工作WITH TIES是将最后一行的所有对等点包括在子句定义的前n中ORDER BY。rank()给出完全相同的结果。
To make sure, I tested with SQL server, here is a live demo.
And here is a more convenient SQLfiddle.
回答by Michael Buen
Try this:
试试这个：
Output: 10, 9, 8, 8
输出：10, 9, 8, 8
with numbers (nums) as (
? values (10), (9), (8), (8), (2)
)?
SELECT nums FROM Numbers?
WHERE nums in (SELECT DISTINCT nums FROM Numbers ORDER BY nums DESC LIMIT 3)
ORDER BY nums DESC
Output: 10,10,9,8,8
输出：10,10,9,8,8
with numbers (nums) as (
values (10), (9), (8), (8), (2), (10)
)
SELECT nums FROM Numbers
WHERE nums in (SELECT DISTINCT nums FROM Numbers ORDER BY nums DESC LIMIT 3)
ORDER BY nums DESC
回答by Lukasz Szozda
PostgreSQL already supports OFFEST FETCHclause and starting from version 13 it will support FETCH FIRST WITH TIES:
PostgreSQL 已经支持OFFEST FETCH子句并且从版本 13 开始它将支持FETCH FIRST WITH TIES：
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]The WITH TIES option is used to return any additional rows that tie for the last place in the result set according to the ORDER BY clause; ORDER BY is mandatory in this case.
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]WITH TIES 选项用于根据 ORDER BY 子句返回与结果集中最后一个位置相关的任何附加行；在这种情况下，ORDER BY 是强制性的。
Query:
询问：
SELECT nums
FROM Numbers
ORDER BY nums DESC
FETCH NEXT 3 ROWS WITH TIES;
