如下例所示，查找deptno=20部门中sal出现次数最多的值。
SQL> select sal
2 from
3 (
4 select sal,dense_rank() over(order by cnt desc) as rnk
5 from
6 (
7 select sal,count(*) as cnt
8 from emp
9 where deptno=20
10 group by sal
11 )
12 )
13 where rnk=1;
SAL
----------
3000
下面把这个查询分解一下，一步步看看分别得到了什么结果，便于理解。
SQL> select sal,count(*) as cnt
2 from emp
3 where deptno=20
4 group by sal;
SAL CNT
---------- ----------
2975 1
1100 1
3000 2
800 1
SQL> select sal,dense_rank() over(order by cnt desc) as rnk
2 from
3 (
4 select sal,count(*) as cnt
5 from emp
6 where deptno=20
7 group by sal
8 );
SAL RNK
---------- ----------
3000 1
800 2
2975 2
1100 2
对于ORACLE 9i以及更高版本，也可以使用聚集函数MAX的KEEP扩展。
SQL> select max(sal) keep(dense_rank first order by cnt desc) sal
2 from
3 (
4 select sal,count(*) as cnt
5 from emp
6 where deptno=20
7 group by sal
8 );
SAL---------- 3000
如果觉得《查找出现次数 oracle ORACLE计算某个列中出现次数最多的值》对你有帮助，请点赞、收藏，并留下你的观点哦！
