最近在工作中遇到进行分组排序的需求,然后进行了相关资料的查询,发现在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)