rank()

返回的数据排名会跳跃；

dense_rank()

返回的数据排名不会跳跃；

示例如下：

创建学生表：

create table t_student(id number ,city_no varchar2(30),city_name varchar2(30),city_type varchar2(30),p_city_no varchar2(30),stu_no varchar2(30),stu_name varchar2(30),stu_grade_no varchar2(30),stu_grade_name varchar2(30),stu_class_no varchar2(30),stu_class_name varchar2(30),exam_time varchar2(30),course varchar2(255),score number, PRIMARY KEY ("ID"));

插入数据：

INSERT INTO "TEST"."T_STUDENT" ("ID", "CITY_NO", "CITY_NAME", "CITY_TYPE", "P_CITY_NO", "STU_NO", "STU_NAME", "STU_GRADE_NO", "STU_GRADE_NAME", "STU_CLASS_NO", "STU_CLASS_NAME", "EXAM_TIME", "COURSE", "SCORE") VALUES ('1', '1001', '福建省', '2', '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '90');INSERT INTO "TEST"."T_STUDENT" ("ID", "CITY_NO", "CITY_NAME", "CITY_TYPE", "P_CITY_NO", "STU_NO", "STU_NAME", "STU_GRADE_NO", "STU_GRADE_NAME", "STU_CLASS_NO", "STU_CLASS_NAME", "EXAM_TIME", "COURSE", "SCORE") VALUES ('2', '100101', '厦门市', '3', '1001', '202001', '夏依', '1', '三年级', '1', '一班', '202201', '语文', '99');INSERT INTO "TEST"."T_STUDENT" ("ID", "CITY_NO", "CITY_NAME", "CITY_TYPE", "P_CITY_NO", "STU_NO", "STU_NAME", "STU_GRADE_NO", "STU_GRADE_NAME", "STU_CLASS_NO", "STU_CLASS_NAME", "EXAM_TIME", "COURSE", "SCORE") VALUES ('3', '10010101', '思明区', '4', '100101', '202002', '思宁', '1', '三年级', '1', '一班', '202202', '语文', '101');INSERT INTO "TEST"."T_STUDENT" ("ID", "CITY_NO", "CITY_NAME", "CITY_TYPE", "P_CITY_NO", "STU_NO", "STU_NAME", "STU_GRADE_NO", "STU_GRADE_NAME", "STU_CLASS_NO", "STU_CLASS_NAME", "EXAM_TIME", "COURSE", "SCORE") VALUES ('4', '10010102', '湖里区', '4', '100101', '202003', '胡明', '1', '三年级', '1', '一班', '202201', '语文', '111');INSERT INTO "TEST"."T_STUDENT" ("ID", "CITY_NO", "CITY_NAME", "CITY_TYPE", "P_CITY_NO", "STU_NO", "STU_NAME", "STU_GRADE_NO", "STU_GRADE_NAME", "STU_CLASS_NO", "STU_CLASS_NAME", "EXAM_TIME", "COURSE", "SCORE") VALUES ('5', '10010103', '同安区', '4', '100101', '202004', '安然', '1', '三年级', '2', '二班', '202201', '数学', '120');INSERT INTO "TEST"."T_STUDENT" ("ID", "CITY_NO", "CITY_NAME", "CITY_TYPE", "P_CITY_NO", "STU_NO", "STU_NAME", "STU_GRADE_NO", "STU_GRADE_NAME", "STU_CLASS_NO", "STU_CLASS_NAME", "EXAM_TIME", "COURSE", "SCORE") VALUES ('6', '10010104', '集美区', '4', '100101', '202005', '郭美美', '1', '三年级', '1', '一班', '202202', '数学', '103');INSERT INTO "TEST"."T_STUDENT" ("ID", "CITY_NO", "CITY_NAME", "CITY_TYPE", "P_CITY_NO", "STU_NO", "STU_NAME", "STU_GRADE_NO", "STU_GRADE_NAME", "STU_CLASS_NO", "STU_CLASS_NAME", "EXAM_TIME", "COURSE", "SCORE") VALUES ('7', '10010105', '海沧区', '4', '100101', '202006', '李沧海', '1', '三年级', '1', '一班', '202202', '英语', '50');INSERT INTO "TEST"."T_STUDENT" ("ID", "CITY_NO", "CITY_NAME", "CITY_TYPE", "P_CITY_NO", "STU_NO", "STU_NAME", "STU_GRADE_NO", "STU_GRADE_NAME", "STU_CLASS_NO", "STU_CLASS_NAME", "EXAM_TIME", "COURSE", "SCORE") VALUES ('8', '10010106', '翔安区', '4', '100101', '202007', '李翔', '1', '三年级', '2', '二班', '202202', '英语', '90');

oracle原始数据

根据分数排名：

select stu_name,       score ,       rank() over(order by score ) rank,       dense_rank() over(order by score ) dense_rank,       row_number() over(order by score ) row_number  from T_STUDENT ;

结果

oracle rank