查询SCOTT公司中每个部门工资最高的员工列表。
select *
from emp
where (deptno,sal)= any(select deptno, max(sal) from emp group by deptno)
select *
from emp
where (deptno,sal) in (
select deptno,max(sal)
from emp
group by deptno
having max(sal) is not null)
select a.*
from emp a, (select deptno, max(sal) maxsal
from emp
group by deptno
) b
where a.deptno=b.deptno and a.sal=b.maxsal
查询SCOTT公司中没有员工的部门。
select *
from dept
where not exists ( select 1 from emp where deptno=dept.deptno)
查询SCOTT公司工资最高的3个员工。
(1)
select empno,ename, hiredate
from (
select empno,ename, hiredate
from emp
where hiredate is not null
order by hiredate desc)
where rownum<=3
(2)
select a.*
from
(
select empno, ename, sal, dense_rank() over (order by sal desc) RANKNO
from emp
where sal is not null
) a
where a.rankno<=3
--- 查询SCOTT公司部门平均工资最高的2个部门
select *
from
(
select deptno, avg(sal), dense_rank() over (order by avg(sal) desc) RANKNO
from emp
where sal is not null
group by deptno
)
where rankno<=2
-- 查询SCOTT公司中每个部门工资最高的2个员工:
select empno, ename, deptno, sal, rankno
from