语法图

代码

rank

RANK为窗口函数，用于对数据的实时分析。

• RANK函数列出相同并列值，并对下一顺序值跳号，例如1，2，3，3，3，6，7，8......

查看RANK函数与DENSE_RANK函数的区别。 查看RANK函数与ROW_NUMBER函数的区别。

query_partition_clause|order_by_clause

窗口函数通用语法。

示例

CREATE TABLE student
(student_no INT PRIMARY KEY,
 class_no INT ,
 student_name VARCHAR2(200) NOT NULL,
 money INT);
INSERT INTO student VALUES (1, 60, 'xiao ming', 4200);
INSERT INTO student VALUES (2, 60, 'xiao huang', 4800);
INSERT INTO student VALUES (3, 60, 'xiao hong', 4800);
INSERT INTO student VALUES (4, 60, 'xiao hua', 6000);
INSERT INTO student VALUES (5, 60, 'xiao chen', 9000);

--student表中包含如下字段和数据
SELECT * FROM student;

  STUDENT_NO     CLASS_NO STUDENT_NAME                                                            MONEY
------------ ------------ ---------------------------------------------------------------- ------------
           1           60 xiao ming                                                                4200
           2           60 xiao huang                                                               4800
           3           60 xiao hong                                                                4800
           4           60 xiao hua                                                                 6000
           5           60 xiao chen                                                                9000


--按CLASS_NO进行分组，并在组内按MONEY进行排序
SELECT STUDENT_NO, CLASS_NO, STUDENT_NAME, MONEY,
       RANK() OVER (PARTITION BY CLASS_NO ORDER BY MONEY) RANK
FROM student WHERE CLASS_NO = 60
ORDER BY RANK, STUDENT_NAME;

  STUDENT_NO     CLASS_NO STUDENT_NAME                                                            MONEY                  RANK
------------ ------------ ---------------------------------------------------------------- ------------ ---------------------
           1           60 xiao ming                                                                4200                     1
           3           60 xiao hong                                                                4800                     2
           2           60 xiao huang                                                               4800                     2
           4           60 xiao hua                                                                 6000                     4
           5           60 xiao chen                                                                9000                     5