oracle数据库rank over用法,Oracle 中分析函数用法之--rank(),dense_rank(),partition,over()

rank()对表中数据进行分级排序

比如有张学生表：Student

SQL> conn scott/tiger

Connected.

SQL> desc student;

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

NAME                                               VARCHAR2(10)

SUBJECT                                            VARCHAR2(20)

GRADE                                              NUMBER

SQL>  SELECT * FROM student ORDER BY subject;

NAME       SUBJECT                   GRADE

---------- --------------------             ----------

zhangsan   chinses                        90

lisi                chinses                        94

wang           chinses                        85

zhangsan   english                          75

lisi                english                          89

zhangsan   maths                            80

lisi                maths                            72

lisi                maths                            98

8 rows selected.

如果要想得到表中chinses 、english和maths各前两名的成绩，那么可以通过rank()函数实现

--首先通过subject分类，然后通过grade列降序SQL> SELECT rank() over(partition by subject order by grade desc) rk,s.* FROM student s;

RK NAME       SUBJECT                   GRADE

---------- ---------- --------------------            ----------

1 lisi                chinses                      94

2 zhangsan   chinses                      90

3 wang           chinses                      85

1 lisi                english                       89

2 zhangsan   english                       75

1 lisi                maths                         98

2 zhangsan   maths                         80

3 lisi                maths                         72

8 rows selected.

然后再加过滤条件rk<=2SQL> SELECT * FROM

2  (SELECT rank() over(partition by subject order by grade desc) rk,s.* FROM student s)

3  tmptb

4  WHERE tmptb.rk<=2;

RK NAME       SUBJECT                   GRADE

---------- ---------- --------------------            ----------

1 lisi                chinses                      94

2 zhangsan   chinses                      90

1 lisi                english                       89

2 zhangsan   english                       75

1 lisi                maths                         98

2 zhangsan   maths                         80

6 rows selected.

dense_rank()的用法，取科目为chinses的前两名成绩"afiedt.buf" 9L, 138C written

1  SELECT *

2  FROM (

3  SELECT name,subject,grade,

4  dense_rank() over(ORDER BY grade DESC) rn

5  FROM student

6  WHERE subject='chinses'

7  )

8* WHERE rn<=2

SQL> /

NAME          SUBJECT       GRADE         RN

----------   -------------------- ----------     ----------

lisi                chinses            94               1

zhangsan   chinses            90               2

row_number()的使用SQL> SELECT row_number() over(order by grade desc) rk,s.* FROM student s;

RK          NAME          SUBJECT                   GRADE

----------         ----------    --------------------              ----------

1             lisi                maths                           98

2             lisi               chinses                         94

3            zhangsan   chinses                         90

4            lisi                english                          89

5            wang           chinses                         85

6            zhangsan   maths                            80

7            zhangsan   english                          75

8            lisi                maths                            72

8 rows selected.

取所有人的前3名SQL> SELECT *

2  FROM (

3  SELECT row_number() over(order by grade desc) rk,s.* FROM student s

4  )

5  WHERE  rk<=3;

RK                    NAME                   SUBJECT                   GRADE

----------               ----------            --------------------                ----------

1                          lisi                         maths                         98

2                          lisi                         chinses                      94

3                         zhangsan             chinses                      90