问题原题:有一个表,包括学生的id,姓名以及成绩。先要求你新增一列,用于表示每个学生的排名。
成绩可能出现重复,要求不能使用RANK函数,不能改变原表的顺序。
这道题还是比较有难度,主要是5.7的mysql不能使用rank函数。
首先我们创建相应的表,以及
create table if not exists `rank`.grade
(
id bigint not null
primary key,
name varchar(20) null,
final_grade int null
);
然后要注意的是,这是原来的表,需要新增一列stu_rank。
ALTER TABLE grade ADD COLUMN stu_rank int null;
我们先创建原始数据集
delimiter $$
create procedure create_data()
begin
declare n bigint default 1;
while n <= 100
DO
INSERT INTO grade value (n, CONCAT('DENNIS', n), RAND() * 100);
SET n = n + 1;
end while;
end $$
delimiter ;
call create_data();
这个存储过程的大致意思,就是添加100行样例数据
RAND的作用是生成一个01的随机数,乘以100则是0100的随机数
然后先上最终的结果(注意这里是暂时没有实现去重的):
delimiter $$
create procedure iterate_set()
begin
-- 用于标识迭代结束
DECLARE finished int default 0;
DECLARE update_id bigint default 0;
DECLARE cur_rank int default 1;
-- 这里使用了游标来对数据进行迭代
DECLARE grade_cursor CURSOR FOR
(SELECT id
FROM grade
ORDER BY final_grade DESC);
-- 迭代结束的触发器
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET finished = 1;
OPEN grade_cursor;
update_rank:
LOOP
FETCH grade_cursor INTO update_id;
IF finished = 1 THEN
LEAVE update_rank;
end if;
UPDATE grade SET stu_rank=cur_rank WHERE id = update_id;
-- 每次的排名增加1
SET cur_rank = cur_rank + 1;
end loop update_rank;
CLOSE grade_cursor;
end $$
delimiter ;
call iterate_set();
这个就是整体的过程了,主要是不能使用RANK函数比较蛋疼
然后根据非重复的,也可以容易推出包含重复数据的排名实现,关键是实现原值的比较:
(这里新建了一个表new_grade,成绩列变为int,这样才可以比较相等,double严格来说无法比较相等)
drop procedure iterate_set;
delimiter $$
create procedure iterate_set()
begin
DECLARE finished int default 0;
DECLARE update_id bigint default 0;
DECLARE cur_rank int default 1;
DECLARE cursor_rank int default 1;
DECLARE cur_val int default 0;
DECLARE pre_val int default -1;
DECLARE grade_cursor CURSOR FOR
(SELECT id, final_grade
FROM new_grade
ORDER BY final_grade DESC);
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET finished = 1;
OPEN grade_cursor;
update_rank:
LOOP
FETCH grade_cursor INTO update_id, cur_val;
IF finished = 1 THEN
LEAVE update_rank;
end if;
IF cur_val <> pre_val THEN
SET cur_rank = cursor_rank;
end if;
UPDATE new_grade SET stu_rank=cur_rank WHERE id = update_id;
SET pre_val = cur_val;
SET cursor_rank = cursor_rank + 1;
end loop update_rank;
CLOSE grade_cursor;
end $$
delimiter ;
call iterate_set();