文章目录
1 数据
数据生成代码
--------------------------------创建表、插入数据----------------------------------
--成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
2 排名
2.1 普通排名
~不分组,直接对分数进行排名,分数相同不并列
SELECT s_id AS "学生ID",
s_score,
@r := @r + 1 AS rank
FROM score s,(SELECT @r := 0) r
ORDER BY s_score DESC;
其中
@r := @r + 1
;SELECT @r := 0
表示生成序列号
SELECT @r := 0
表示初始化序列号为 0@r := @r + 1
表示序列号按 1 递增
2.2 普通并列排名~1123型并列
~直接对分数进行排名,相同分数排名并列
SELECT s_id AS "学生ID",s_score,
CASE WHEN @prerank = s_score THEN @i
WHEN @prerank := s_score THEN @i := @i + 1
END AS rank
FROM score s,(SELECT @prerank := NULL,@i := 0) r
ORDER BY s_score DESC;
= 与 := 的区别
=
:只有在 set 和 update 中时才和 := 一样表示赋值的作用,其他时候都是等于的作用。:=
:在任何时候都表示赋值的作用。
2.3 普通并列排名~1134型并列
函数 | 含义 |
---|---|
IF(expr,v1,v2) | 如果expr表达式为true,返回v1,否则返回v2 |
SELECT s_id AS "学生ID",s_score,rank
FROM
(SELECT s.*,
@c := IF(@p=s_score,@c,@r) AS rank,
@p := s_score,
@r := @r +1
FROM score s,(SELECT @c:=0,@p:=NULL,@r:=1) r
ORDER BY s_score DESC) subquery;
代码解析
@c := IF(@p=s_score,@c,@r) AS rank
表示新增一列变量 rank ,若p=s_score,则 c := c ;否则 c := r@p := s_score
表示 将 p 赋值为 s_score@r := @r +1
表示 将 r 赋值为 r+1(SELECT @c:=0,@p:=NULL,@r:=1)
表示初始化 c p r 的值
3. 分组排名
3.1 分组后组内排名~普通顺序排名
SELECT c_id AS "课程ID",s_id AS "学生ID",s_score,rank
FROM
(SELECT s.*,
IF(@c=c_id,@r:=@r+1,@r:=1) AS rank,
@c:=c_id
FROM score AS s,(SELECT @c:=NULL,@r:=0) AS r
ORDER BY c_id,s_score DESC) AS subquery;
3.2 分组后组内排名~1123型并列排名
SELECT c_id AS "课程ID",s_id AS "学生ID",s_score,rank
FROM
(SELECT s.*,
IF( @c=c_id,
CASE
WHEN @p=s_score THEN @r
WHEN @p:=s_score THEN @r:=@r+1
END,
@r:=1) AS rank,
@c:=c_id,
@p:=s_score
FROM score AS s,
(SELECT @c:=NULL,@p:=NULL,@r:=0) AS r
ORDER BY c_id,s_score DESC) AS subquery;
3.3 分组后组内排名~1134型并列排名
3.4.1 含bug版代码
#一段结果正确但有bug的代码
SELECT c_id AS "课程ID",s_id AS "学生ID",s_score,rank
FROM
(SELECT s.*,
IF( @c=c_id,@k:=@k+1,@k:=1),
CASE
WHEN @p=s_score THEN @r
WHEN @p:=s_score THEN @r:=@k
END AS rank,
@c:=c_id
FROM score AS s,
(SELECT @c:=NULL,@p:=NULL,@r:=0,@k:=0) AS r
ORDER BY c_id,s_score DESC) AS subquery;
###上例代码有个bug,若01最低分与02最高分相同就会出错
#新建score表代码
--成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--成绩表测试数据
insert into Score values('01' , '01' , 99);
insert into Score values('01' , '02' , 90);
insert into Score values('02' , '01' , 98);
insert into Score values('02' , '02' , 90);
insert into Score values('03' , '01' , 98);
insert into Score values('03' , '02' , 80);
insert into Score values('04' , '01' , 90);
insert into Score values('04' , '02' , 81);
3.4.2 修改版代码
SELECT c_id AS "课程ID",s_id AS "学生ID",s_score,rank
FROM
(SELECT s.*,
IF( @c=c_id,@k:=@k+1,@k:=1),
IF( @c=c_id,
CASE
WHEN @p=s_score THEN @r
WHEN @p:=s_score THEN @r:=@k
END,
@r:=1) AS rank,
@c:=c_id,
@p:=s_score
FROM score AS s,
(SELECT @c:=NULL,@p:=NULL,@r:=0,@k:=0) AS r
ORDER BY c_id,s_score DESC) AS subquery;
参考:
https://www.cnblogs.com/niniya/p/9046449.html
https://blog.csdn.net/csdnluolei/article/details/83507312