SQL> select * from employee;
ID DEPTID NAME SALARY
---------- ---------- -------------------- ----------
5 2 e 400
6 2 f 200
1 1 a 100
2 1 b 200
3 2 c 300
4 2 d 300
6 rows selected.
Elapsed: 00:00:00.01
SQL> select *
2 from (select e1.deptid,
3 e1.name,
4 e1.salary,
5 dense_rank() over(partition by e1.deptid order by e1.salary desc) rn
6 from employee e1)
7 where rn <= 4;
DEPTID NAME SALARY RN
---------- -------------------- ---------- ----------
1 b 200 1
1 a 100 2
2 e 400 1
2 c 300 2
2 d 300 2
2 f 200 3
6 rows selected.
Elapsed: 00:00:00.00
SQL> edit
Wrote file afiedt.buf
1 select *
2 from (select e1.deptid,
3 e1.name,
4 e1.salary,
5 rank() over(partition by e1.deptid order by e1.salary desc) rn
6 from employee e1)
7* where rn <= 4
SQL> /
DEPTID NAME SALARY RN
---------- -------------------- ---------- ----------
1 b 200 1
1 a 100 2
2 e 400 1
2 c 300 2
2 d 300 2
2 f 200 4
6 rows selected.
Elapsed: 00:00:00.03
SQL> edit
Wrote file afiedt.buf
1 select *
2 from (select e1.deptid,
3 e1.name,
4 e1.salary,
5 row_number() over(partition by e1.deptid order by e1.salary desc) rn
6 from employee e1)
7* where rn <= 4
SQL> /
DEPTID NAME SALARY RN
---------- -------------------- ---------- ----------
1 b 200 1
1 a 100 2
2 e 400 1
2 c 300 2
2 d 300 3
2 f 200 4
6 rows selected.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22034023/viewspace-673847/ ，如需转载，请注明出处，否则将追究法律责任。
