row_number()over()的用法
以下是个测试的例子：
SQL> select * from employees;
DEPT_ID NAME EMP_ID
---------- -------------------- ----------
10 a 1001
10 b 1003
20 c 1002
20 d 1004
30 e 1006
20 d 1005
30 f 1007
40 g 1008
40 h 1011
60 i 1009
50 j 1010
DEPT_ID NAME EMP_ID
---------- -------------------- ----------
50 k 1012
20 l 1013
已选择13行。
SQL> select * from
2 (select rownum m,employees.* from employees where rownum <=5)
3 where m>=2;
M DEPT_ID NAME EMP_ID
---------- ---------- -------------------- ----------
2 10 b 1003
3 20 c 1002
4 20 d 1004
5 30 e 1006
SQL> select * from
2 (select employees.*,row_number()over(order by emp_id) rowcount from employees)
3 where rowcount<=10;
DEPT_ID NAME EMP_ID ROWCOUNT
---------- -------------------- ---------- ----------
10 a 1001 1
20 c 1002 2
10 b 1003 3
20 d 1004 4
20 d 1005 5
30 e 1006 6
30 f 1007 7
40 g 1008 8
60 i 1009 9
50 j 1010 10
SQL> select * from
2 (select employees.*,row_number()over(partition by dept_id order by emp_id) rowcount from employees)
3 where rowcount<=3;
DEPT_ID NAME EMP_ID ROWCOUNT
---------- -------------------- ---------- ----------
10 a 1001 1
10 b 1003 2
20 c 1002 1
20 d 1004 2
20 d 1005 3
30 e 1006 1
30 f 1007 2
40 g 1008 1
40 h 1011 2
50 j 1010 1
50 k 1012 2
DEPT_ID NAME EMP_ID ROWCOUNT
---------- -------------------- ---------- ----------
60 i 1009 1
SQL> select * from
2 (select employees.*,row_number()over(partition by dept_id order by emp_id) rowcount from employees)
3 where rowcount<=10;
DEPT_ID NAME EMP_ID ROWCOUNT
---------- -------------------- ---------- ----------
10 a 1001 1
10 b 1003 2
20 c 1002 1
20 d 1004 2
20 d 1005 3
20 l 1013 4
30 e 1006 1
30 f 1007 2
40 g 1008 1
40 h 1011 2
50 j 1010 1
DEPT_ID NAME EMP_ID ROWCOUNT
---------- -------------------- ---------- ----------
50 k 1012 2
60 i 1009 1
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12361284/viewspace-165717/ ，如需转载，请注明出处，否则将追究法律责任。
