语法图

代码

row_number

ROW_NUMBER为窗口函数，其语法描述及约束与RANK，DENSE_RANK函数一致，区别在于排序时对并列值的处理：

• RANK函数列出相同并列值，并对下一顺序值跳号，例如1，2，3，3，3，6，7，8......
• DENSE_RANK函数列出相同并列值，并对下一顺序值不跳号，例如1，2，3，3，3，4，5，6......
• ROW_NUMBER函数不列出并列值，而是根据返回的结果递增，不跳号，例如1，2，3，4，5，6，7，8......

示例

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,
       ROW_NUMBER() OVER (PARTITION BY CLASS_NO ORDER BY MONEY) ROW_NUMBER
FROM student WHERE CLASS_NO = 60
ORDER BY ROW_NUMBER, STUDENT_NAME;

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