----列出部门ID和最大薪资值
---1 最常规的方法
select e.deptno, max(e.sal)
from emp e
group by e.deptno;
---2、分析函数实现
select *
from (select e.deptno,
e.sal,
row_number() over(partition by e.deptno order by e.sal desc) as rn
from emp e)
where rn = 1;
---备注:row_number()over() 分析函数如果存在重复的salary的话 会被过滤掉
---需求更改成:列出各部门最高薪资以部门id
/*这个是后才有方法1的实现方式肯定不行
*/
---方法1的实现(不可以)
select e.deptno,e.ename ,max(e.sal)
from emp e
group by e.department_id,e.ename;---group by 里面不能使用别名,order by 可以
---子查询方法实现
select e.sal, e.deptno
from emp e
where (e.deptno, e.sal) in
(select em.deptno, max(em.sal)
from emp em
group by em.deptno)
---分析函数实现(不过滤重复值)
select *
from (select e.department_id,
e.salary,
dense_rank() over(partition by e.department_id order by e.salary desc) as rn
from employees e)
where rn = 1;---employees 表最大工资不存在重复的 ,我们换一个emp
----也取最大重复值
select *
from (select e.deptno,
e.sal,
dense_rank() over(partition by e.deptno order by e.sal desc) as rn
from emp e)
where rn = 1;
----不取最大重复值
select *
from (select e.deptno,
e.sal,
row_number() over(partition by e.deptno order by e.sal desc) as rn
from emp e)
where rn = 1;
----max()over() 函数实现
select *
from (select e.deptno,
e.sal,
max(e.sal) over(partition by e.deptno order by e.sal desc) as rn
from emp e)
where rn = sal;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29507357/viewspace-1222543/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29507357/viewspace-1222543/