#DENSE_RANK

dense_rank
DENSE_RANK ( ) OVER ( query_partition_clause order_by_clause )

DENSE_RANK为窗口函数,用于对数据的实时分析。

  • DENSE_RANK函数列出相同并列值,并对下一顺序值不跳号,例如1,2,3,3,3,4,5,6......

查看DENSE_RANK函数与RANK函数的区别查看DENSE_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,
       DENSE_RANK() OVER (PARTITION BY CLASS_NO ORDER BY MONEY) DENSE_RANK
FROM student WHERE CLASS_NO = 60
ORDER BY DENSE_RANK, STUDENT_NAME;

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


Copied!
pdf-btn 下载文档
copy-btn 复制链接