ORACLE计算某个列中出现次数最多的值

如下例所示，查找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