Oracle 中一列的前 n 个不同值
我正在使用一个查询，其中一部分获取特定列的前 3 个.
I'm using a query where a part of it gets the top 3 of a certain column.
它创建列的不同子查询，限制为 3 行，然后将这些行过滤到主查询以执行前 3 行.
It creates a distinct subquery of the column, limited by 3 number of rows, and then filters those rows to the main query to do the top 3.
WITH subquery AS (
SELECT col FROM (
SELECT DISTINCT col
FROM tbl
) WHERE ROWNUM <= 3
)
SELECT col
FROM tbl
WHERE tbl.col = subquery.col
所以原来的表是这样的:
So the original table is like this:
col
-----
a
a
a
b
b
b
c
d
d
e
f
f
f
f
并且查询返回列的前 3 行(不是前 3 行，它只会是 a):
And the query returns the top 3 of the column (not the top 3 rows which would only be a):
col
-----
a
a
a
b
b
b
c
我正在尝试了解是否有更正确的方法来执行此操作，因为实际查询很大，并且使用看起来几乎相同的子查询复制其大小只是为了获得前 3 名很难使用和理解/修改.
I'm trying to learn if there is a more correct way of doing this as the real query is big and duplicating its size with a subquery that looks almost the same just to get the top 3 is hard to work with and understand/modify.
是否有更好的方法来处理 Oracle 中一列的前 3 个不同值?
Is there a better way to do the top first 3 distinct values of one column in Oracle?
推荐答案
是的，您可以使用 dense_rank 并避免重复代码:
Yes, you can use dense_rank and avoid duplicated code:
select col
from (select col, dense_rank() over (order by col) rnk from tbl)
where rnk <= 3
演示
相关文章