postgresql SQL - min() 获得最低值，max() 获得最高值，如果我想要第 2 个（或第 5 个或第 n 个）最低值怎么办？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/502794/
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
SQL - min() gets the lowest value, max() the highest, what if I want the 2nd (or 5th or nth) lowest value?
提问by Rory
The problem I'm trying to solve is that I have a table like this:
我试图解决的问题是我有一张这样的表：
a and b refer to point on a different table. distance is the distance between the points.
a 和 b 指的是不同表上的点。距离是点之间的距离。
| id | a_id | b_id | distance | delete |
| 1 | 1 | 1 | 1 | 0 |
| 2 | 1 | 2 | 0.2345 | 0 |
| 3 | 1 | 3 | 100 | 0 |
| 4 | 2 | 1 | 1343.2 | 0 |
| 5 | 2 | 2 | 0.45 | 0 |
| 6 | 2 | 3 | 110 | 0 |
....
The important column I'm looking is a_id. If I wanted to keep the closet b for each a, I could do something like this:
我正在查找的重要列是 a_id。如果我想为每个 a 保留壁橱 b，我可以这样做：
update mytable set delete = 1 from (select a_id, min(distance) as dist from table group by a_id) as x where a_gid = a_gid and distance > dist;
delete from mytable where delete = 1;
Which would give me a result table like this:
这会给我一个这样的结果表：
| id | a_id | b_id | distance | delete |
| 1 | 1 | 1 | 1 | 0 |
| 5 | 2 | 2 | 0.45 | 0 |
....
i.e. I need one row for each value of a_id, and that row should have the lowest value of distance for each a_id.
即我需要为 a_id 的每个值一行，并且该行应该具有每个 a_id 的最低距离值。
However I want to keep the 10 closest points for each a_gid. I could do this with a plpgsql function but I'm curious if there is a more SQL-y way.
但是我想为每个 a_gid 保留 10 个最近的点。我可以用 plpgsql 函数来做到这一点，但我很好奇是否有更多的 SQL-y 方式。
min() and max() return the smallest and largest, if there was an aggregate function like nth(), which'd return the nth largest/smallest value then I could do this in similar manner to the above.
min() 和 max() 返回最小和最大，如果有一个像 nth() 这样的聚合函数，它会返回第 n 个最大/最小的值，那么我可以以与上述类似的方式执行此操作。
I'm using PostgeSQL.
我正在使用 PostgeSQL。
采纳答案by Quassnoi
Try this:
尝试这个：
SELECT *
FROM (
SELECT a_id, (
SELECT b_id
FROM mytable mib
WHERE mib.a_id = ma.a_id
ORDER BY
dist DESC
LIMIT 1 OFFSET s
) AS b_id
FROM (
SELECT DISTINCT a_id
FROM mytable mia
) ma, generate_series (1, 10) s
) ab
WHERE b_id IS NOT NULL
Checked on PostgreSQL 8.3
在 PostgreSQL 8.3 上检查
回答by Elijah
I love postgres, so it took it as a challenge the second I saw this question.
我喜欢 postgres，所以当我看到这个问题的时候就把它当作了一个挑战。
So, for the table:
所以，对于表：
Table "pg_temp_29.foo"
Column | Type | Modifiers
--------+---------+-----------
value | integer |
With the values:
值：
SELECT value FROM foo ORDER BY value;
value
-------
0
1
2
3
4
5
6
7
8
9
14
20
32
(13 rows)
You can do a:
你可以做一个：
SELECT value FROM foo ORDER BY value DESC LIMIT 1 OFFSET X
Where X = 0 for the highest value, 1 for the second highest, 2... And so forth.
其中 X = 0 表示最高值，1 表示次高值，2... 依此类推。
This can be further embedded in a subquery to retrieve the value needed. So, to use the dataset provided in the original question we can get the a_ids with the top ten lowest distances by doing:
这可以进一步嵌入到子查询中以检索所需的值。因此，要使用原始问题中提供的数据集，我们可以通过执行以下操作获得前十个最低距离的 a_ids：
SELECT a_id, distance FROM mytable
WHERE id IN
(SELECT id FROM mytable WHERE t1.a_id = t2.a_id
ORDER BY distance LIMIT 10);
ORDER BY a_id, distance;
a_id | distance
------+----------
1 | 0.2345
1 | 1
1 | 100
2 | 0.45
2 | 110
2 | 1342.2
回答by Zinan Xing
This SQL should find you the Nth lowest salary should work in SQL Server, MySQL, DB2, Oracle, Teradata, and almost any other RDBMS: (note: low performance because of subquery)
这条 SQL 应该找到你第 N 个最低薪水 应该在 SQL Server、MySQL、DB2、Oracle、Teradata 和几乎所有其他 RDBMS 中工作：（注意：由于子查询导致性能低下）
SELECT * /*This is the outer query part */
FROM mytable tbl1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(tbl2.distance))
FROM mytable tbl2
WHERE tbl2.distance < tbl1.distance)
The most important thing to understand in the query above is that the subquery is evaluated each and every time a row is processed by the outer query. In other words, the inner query can not be processed independently of the outer query since the inner query uses the tbl1 value as well.
在上面的查询中要理解的最重要的事情是每次外部查询处理一行时都会评估子查询。换句话说，内部查询不能独立于外部查询进行处理，因为内部查询也使用 tbl1 值。
In order to find the Nth lowest value, we just find the value that has exactly N-1 values lower than itself.
为了找到第 N 个最低值，我们只需找到正好比其本身低 N-1 个值的值。
回答by Tony Andrews
Does PostgreSQL have the analytic function rank()? If so try:
PostgreSQL 有解析函数 rank() 吗？如果是这样，请尝试：
select a_id, b_id, distance
from
( select a_id, b_id, distance, rank() over (partition by a_id order by distance) rnk
from mytable
) where rnk <= 10;
