ITPub博客

首页 > Linux操作系统 > Linux操作系统 > row_number()over()的用法

row_number()over()的用法

原创 Linux操作系统 作者:oracle_ace 时间:2008-01-30 12:54:56 0 删除 编辑

以下是个测试的例子:

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博客 ” ,链接:http://blog.itpub.net/12361284/viewspace-165717/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 初探ADDM的使用
请登录后发表评论 登录
全部评论

注册时间:2007-12-10

  • 博文量
    284
  • 访问量
    796988