TOP n WITH TIES 的 PostgreSQL 等价物:LIMIT “with ties"? [英] PostgreSQL equivalent for TOP n WITH TIES: LIMIT "with ties"?

问题描述

我正在 SQL Server 中寻找类似的东西:

I'm looking for something similar this in SQL Server:

SELECT TOP n WITH TIES FROM tablename

我知道 PostgreSQL 中的 LIMIT ，但是否存在上述等效项?我只是好奇，因为它每次都会为我节省一个额外的查询.

I know about LIMIT in PostgreSQL, but does the equivalent of the above exist? I'm just curious as it would save an extra query each time for me.

如果我有一个带有属性 nums 的表 Numbers:{10, 9, 8, 8, 2}.我想做类似的事情:

If I have a table Numbers with attribute nums: {10, 9, 8, 8, 2}. I want to do something like:

SELECT nums FROM Numbers ORDER BY nums DESC LIMIT *with ties* 3

它应该返回 {10, 9, 8, 8} 因为它需要前 3 个加上额外的 8 因为它与另一个联系在一起.

It should return {10, 9, 8, 8} because it takes the top 3 plus the extra 8 since it ties the other one.

推荐答案

Postgres 13 终于添加了 WITH TIES .见:

Postgres 13 finally adds WITH TIES . See:

• 大于或等于 ALL() 等于 MAX() 速度

没有没有WITH TIES PostgreSQL 12 之前的子句，就像 SQL Server.
在 PostgreSQL 中，我会将其替换为 TOP n WITH TIES .. ORDER BY <something>:

There is no WITH TIES clause up to PostgreSQL 12, like there is in SQL Server.
In PostgreSQL I would substitute this for 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;

要清楚，rank() 是对的，dense_rank() 会出错(返回太多行).
考虑一下 SQL Server 文档中的引用(来自上面的链接):

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):

例如，如果表达式设置为 5 但另外 2 行匹配第 5 行 ORDER BY 列的值，结果集将包含 7 行.

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.

WITH TIES 的工作是将最后一行的所有对等点包含在由 ORDER BY 子句定义的顶部 n 中.rank() 给出完全相同的结果.

The job of WITH TIES is to include all peers of the last row in the top n as defined by the ORDER BY clause. rank() gives the exact same result.

为了确保，我使用 SQL 服务器进行了测试，这里是一个 现场演示.
这是一个更方便的SQLfiddle.

To make sure, I tested with SQL server, here is a live demo.
And here is a more convenient SQLfiddle.

这篇关于TOP n WITH TIES 的 PostgreSQL 等价物:LIMIT “with ties"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！