Oracle的分组排序-Partition By

       最近在工作中遇到进行分组排序的需求,然后进行了相关资料的查询,发现在Oracle中Partition By基本可以完美解决我的需求,现在总结分享一下.

准备建表数据:

CREATE TABLE "TBL_STUDENNT_SCORE" (
  "ID" NUMBER(16,0) NOT NULL,
  "STU_ID" NUMBER(16,0),
  "STU_NAME" VARCHAR2(255 BYTE),
  "SUBJECT" VARCHAR2(255 BYTE),
  "SCORE" NUMBER(16,0)
);

COMMENT ON COLUMN "IRONMAN"."TBL_STUDENNT_SCORE"."STU_ID" IS '学生成绩';
COMMENT ON COLUMN "IRONMAN"."TBL_STUDENNT_SCORE"."STU_NAME" IS '学生姓名';
COMMENT ON COLUMN "IRONMAN"."TBL_STUDENNT_SCORE"."SUBJECT" IS '课程';
COMMENT ON COLUMN "IRONMAN"."TBL_STUDENNT_SCORE"."SCORE" IS '分数';

-- ----------------------------
INSERT INTO "TBL_STUDENNT_SCORE" VALUES ('1', '23', '貂蝉', '数学', '65');
INSERT INTO "TBL_STUDENNT_SCORE" VALUES ('2', '34', '夏侯惇', '数学', '85');
INSERT INTO "TBL_STUDENNT_SCORE" VALUES ('3', '35', '关羽', '数学', '80');
INSERT INTO "TBL_STUDENNT_SCORE" VALUES ('4', '35', '关羽', '语文', '71');
INSERT INTO "TBL_STUDENNT_SCORE" VALUES ('5', '21', '孙尚香', '语文', '98');
INSERT INTO "TBL_STUDENNT_SCORE" VALUES ('6', '37', '周瑜', '语文', '71');
INSERT INTO "TBL_STUDENNT_SCORE" VALUES ('7', '22', '沈梦溪', '数学', '83');
INSERT INTO "TBL_STUDENNT_SCORE" VALUES ('8', '26', '花木兰', '语文', '70');

需求

按照科目,对学生的成绩进行从高到低排序.

 

解决方案

SELECT * FROM (
SELECT 
row_number() over(partition by subject ORDER BY score desc) 
score_rank ,
stu_name,score ,subject from TBL_STUDENNT_SCORE ) 

执行结果为:

此时我们发现,周瑜和关羽的语文成绩都一样,但一个排名是2,一个是3.一般来说,相同分数应该有相同的排名.因此,可以将sql改成

SELECT * FROM (
SELECT 
rank() over(partition by subject ORDER BY score desc) 
score_rank ,
stu_name,score ,subject from TBL_STUDENNT_SCORE )

执行结果为:

此时周瑜和关羽的排名一样,都是并列2.但此时,花木兰觉得,自己的分数应该是排名第3,而不应该是4.需要解决花木兰的困惑,我们可以使用如下SQL:

SELECT * FROM (
SELECT 
dense_rank() over(partition by subject ORDER BY score desc) 
score_rank ,
stu_name,score ,subject from TBL_STUDENNT_SCORE )

执行结果为:

此时,大家对排名都比较满意.

总结

我们发现,以上三句SQL的区别就是使用函数不同,

--ROW_NUMBER() 顺序排序

--RANK() （跳跃排序,排序为 1,1,3）

--DENSE_RANK()（跳跃排序，排序为 1,1,2）