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 ;
Oracle 中分组排序取值的问题
2:rank() over()跳跃排序
对于row_number() over() 来说,如果两个值相等,区分不出前后,如下图所示:
Oracle 中分组排序取值的问题

rank() over()是跳跃排序的,是可以有两个第二名的,后面跟着第三名;

SELECT t.name,t.subject,t.score, rank() OVER (PARTITION BY t.subject ORDER BY t.score DESC) rn FROM exam t ;

Oracle 中分组排序取值的问题

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 ;

Oracle 中分组排序取值的问题

 


上一篇:jTopo HTML5 Canvas 画图组件


下一篇:HTML5 Canvas画图与动画学习59例