【SQL】分析函数功能-排序

1:排名,不考虑并列问题 row_number()
2:排名,有并列,并列后的排名不连续 rank()

3:排名,有并列,并列后的排名连续 dense_rank()

 

测试:
SQL> create table test1
  2  (id number,
  3  name varchar2(10),
  4  salary number);
Table created          --创建测试表;

插入输入数据:
SQL> insert into test1 values(1,'zhangsan',1000);
1 row inserted
SQL> insert into test1 values(2,'lisi',1200);
1 row inserted
SQL> insert into test1 values(3,'wangwu',1200);
1 row inserted
SQL> insert into test1 values(3,'zhaosi',2000);
1 row inserted

查看表格:
SQL> SELECT ID,name,salary,
  2  row_number() OVER (ORDER BY salary DESC)o1,
  3  RANK() OVER (ORDER BY salary DESC) o2,
  4  dense_rank() OVER (ORDER BY salary DESC) o3
  5  FROM test1;
        ID NAME     SALARY         O1         O2         O3
---------- ---------- ---------- ---------- ---------- ----------
         3 zhaosi       2000          1          1          1
         2 lisi             1200          2          2          2
         3 wangwu       1200          3          2          2
         1 zhangsan       1000          4          4          3


 

posted on 2016-12-16 17:22  Diegoal  阅读(238)  评论(0)    收藏  举报

导航