Oracle中一列的前n个不同值 [英] Top n distinct values of one column in Oracle
问题描述
我正在使用查询,查询的一部分获得某一列的前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
原始表格如下:
col
-----
a
a
a
b
b
b
c
d
d
e
f
f
f
f
查询返回该列的前3名(而不是仅前a
的前3行):
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
我正在尝试了解是否存在更正确的方法,因为真正的查询很大,并且使用看起来几乎相同的子查询来复制它的大小,只是为了获得前三名很难并且难以理解/modify.
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
这篇关于Oracle中一列的前n个不同值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!