Oracle分析函数--排序

2017-01-13 08:18:49来源:CSDN作者:qq_28259083人点击

RANK,对相同的数据会排相同的序号,但在下一条记录时,会进行跳号处理;
DENSE_RANK,对相同的数据会排相同的序号,但不会跳号;
ROW_NUMBER ,不管记录是否相同,总是会依序排列。
RANK、DENSE_RANK、ROW_NUMBER都需要与OVER一起使用。

首先,建立数据表,并插入测试数据:

CREATE TABLE STUDENT(STUNO VARCHAR(12) PRIMARY KEY,STUNAME VARCHAR(12),GRADE VARCHAR(2),SCORE FLOAT);INSERT INTO STUDENT VALUES('201227240101','李斯','1','76');INSERT INTO STUDENT VALUES('201227240102','刘柳','1','68');INSERT INTO STUDENT VALUES('201227240103','李兴','1','70');INSERT INTO STUDENT VALUES('201227240104','赵四','1','70');INSERT INTO STUDENT VALUES('201227240201','张三','2','70');INSERT INTO STUDENT VALUES('201227240202','陈小','2','78');INSERT INTO STUDENT VALUES('201227240203','李师师','2','78');INSERT INTO STUDENT VALUES('201227240204','李鱼','2','80');

然后,

SELECT rank() OVER(ORDER BY SCORE)sort ,STUNO,STUNAME,GRADE,SCORE FROM student;

查询结果集:
这里写图片描述

SELECT dense_rank() OVER(ORDER BY SCORE)sort ,STUNO,STUNAME,GRADE,SCORE FROM student;

查询结果集:
这里写图片描述

SELECT row_number() OVER(ORDER BY SCORE)sort ,STUNO,STUNAME,GRADE,SCORE FROM student;

查询结果集:
这里写图片描述

也可以向以上查询语句中添加partition by
子句指定分区。当不指定分区子句时,会
将全部数据作为一个大的分区。当指定分区时,
每切换一个分区,都
会重新进行排序。以下语句指定GRADE作为分区

SELECT rank() OVER(partition by grade ORDER BY SCORE)sort ,STUNO,STUNAME,GRADE,SCORE FROM student;

结果集:
这里写图片描述

SELECT dense_rank() OVER(partition by grade ORDER BY SCORE)sort ,STUNO,STUNAME,GRADE,SCORE FROM student;SELECT row_number() OVER(partition by grade ORDER BY SCORE)sort ,STUNO,STUNAME,GRADE,SCORE FROM student;

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台