--先删除原先存在的表:
drop table emp;
--创建表emp
create table emp
(
deptno number,
ename varchar2(20),
sal number
);
--插入数据
--部门1的员工信息
insert into emp values(1,'王嘻嘻1',15000);
insert into emp values(1,'王嘻嘻2',1500);
insert into emp values(1,'王嘻嘻3',115000);
insert into emp values(1,'王嘻嘻4',12300);
--部门2的员工信息
insert into emp values(2,'王哈哈',14300);
insert into emp values(2,'王呵呵',16300);
insert into emp values(2,'王么么',11300);
insert into emp values(2,'王大大',10300);
select * from emp;
--选择每一个部门的前3名最高的工资人的信息
select * from
(select deptno,ename,sal,
row_number() over(
partition by deptno
order by sal desc) rn
from emp
)
where rn<=3;
运行如下: