Oracle 中分组排序取值的问题
整理一下排序:
建表语句:
create table EXAM
(
name VARCHAR2(32),
subject VARCHAR2(32),
score INTEGER
)
数据:
INSERT INTO EXAM VALUES ('赵柳', '数学', '71');
INSERT INTO EXAM VALUES ('张三', '数学', '81');
INSERT INTO EXAM VALUES ('李四', '数学', '75');
INSERT INTO EXAM VALUES ('起点', '数学', '88');
INSERT INTO EXAM VALUES ('起点', '英语', '98');
INSERT INTO EXAM VALUES ('李四', '英语', '86');
INSERT INTO EXAM VALUES ('赵柳', '英语', '86');
INSERT INTO EXAM VALUES ('张思', '英语', '33');
INSERT INTO EXAM VALUES ('李四', '语文', '75');
INSERT INTO EXAM VALUES ('张三', '语文', '81');
INSERT INTO EXAM VALUES ('赵柳', '语文', '71');
INSERT INTO EXAM VALUES ('起点', '语文', '88');
1.row_number() over() 的用法:SELECT *,ROW_NUMBER() OVER(PARTITION BY '分组' ORDER BY '排序') RN FROM TABLE;
SELECT t.name,t.subject,t.score, row_number() OVER (PARTITION BY t.subject ORDER BY t.score DESC) rn FROM exam t ;
2:rank() over()跳跃排序
对于row_number() over() 来说,如果两个值相等,区分不出前后,如下图所示:
而rank() over()
是跳跃排序的,是可以有两个第二名的,后面跟着第三名;
SELECT t.name,t.subject,t.score, rank() OVER (PARTITION BY t.subject ORDER BY t.score DESC) rn FROM exam t ;
3:dense_rank() over()
如果想 两个第二名之后跟着是第三名:SELECT t.name,t.subject,t.score, dense_rank() OVER (PARTITION BY t.subject ORDER BY t.score DESC) rn FROM exam t ;