SQL语句之计算次数出现最多的值

需求，计算20号部门员工工资出现的次数
使用count() 函数：

SQL> select sal,count(*) time from emp where deptno=20 group by sal;       SAL   TIME---------- ----------      2975      1      1100      1      3000      2       800      1SQL> 

计算20号部门工资出现的次数，并排出序号
使用dense_rank分析函数

SQL> select sal,dense_rank() over(order by time desc) as seq  2  from   3  (select sal,count(*) time from emp where deptno=20 group by sal) emp;       SAL    SEQ---------- ----------      3000      1       800      2      2975      2      1100      2SQL> 

根据序号过滤得到序号为2的结果

SQL> select sal  2  from  3  (select sal,dense_rank() over(order by time desc) as seq  4  from  5  (select sal,count(*) time from emp where deptno=20 group by sal) emp) emp  6  where seq=2;       SAL----------      2975       800      1100SQL> 

利用partition by 子句分别查询各个部门哪个工资等级的员工多

SQL> select deptno,sal  2  from  3  (select deptno,sal,dense_rank() over(partition by deptno order by time desc) as seq  4  from  5  (select deptno,sal,count(*) time from emp group by deptno,sal) emp ) emp   6  where seq=1;    DEPTNO    SAL---------- ----------    10   5000    10   1300    10   2450    20   3000    30   1250SQL> 

以上就是计算次数出现最多的次数的方法。