一、定义变量排序
目的1:合并学生表和分数表,将每个班分别排名
目的2:遇到相同分数,考虑还是不考虑相同分数排名
学生表(1000条)和分数表(6000条)分别如下
1、定义变量、简答排序
首先先看一段简单代码:
set @i:=0; -- 定义一个变量i,初始化值为1
SELECT
* -- 查询所有的学生表信息
,@i:=@i+1 -- 建立排名列,类似于循环
FROM student
其结果为:除了最后一行的排序字段为新增的,其余的都是student表中的字段
解析代码:
首先使用set定义一个变量 i ,此时要使用@:=来定义,并且定义完要在最后加上分号表示这条语句单独执行
使用select语句查询student表时,创建一个新列用来存放排名,排名从1开始,每行加1,所以可以使用外界定义的变量来建立类似于循环的用法
2、不考虑同分排序
先看代码:
-- 定义两个变量
set @i:=0;
set @last_clazz:='';
SELECT
* -- 输出tt1表的所有字段
,@last_clazz -- 定义一个空字段名,此时一整列数据都为空,配合下面赋值语句的结果为将一整列下移一行
,IF(clazz=@last_clazz,@i:=@i+1,@i:=1) as rank -- 复制了一整列数据后,判断相同行数据是否一致,如果一致则说明还是一个班级里,排名继续增长,如果不一样,则将i值重新定义为1,从头再开始增长
,@last_clazz:=clazz -- 将clazz班级名全部复制一遍定义成一个新列
FROM
-- 下列查询得到的表为将两个表合并成一个表
(SELECT
t1.*
,t2.sum_sco
FROM student t1
JOIN (SELECT id,sum(score)as sum_sco FROM score GROUP BY id) t2
ON t1.id = t2.id
ORDER BY clazz DESC,sum_sco DESC) tt1 -- 先运行括号内的语句,此时已经先对每个班的班级和成绩排好序了,然后在对这整个表进行排名操作
再看结果:
此时可以看出每个班分别的排名情况。
解析代码:
首先,通过 set @i:=0 设置变量 @i 的初始值为 0。这个变量会在后面的查询中被用来计算排名。
接着,通过 set @last_clazz:='' 设置变量 @last_clazz 的初始值为空。这个变量用于保存上一个班级的值,以便与当前班级进行比较。然后,开始执行查询语句。查询的主体部分是一个子查询 SELECT t1.*, t2.sum_sco FROM student t1 JOIN (SELECT id, sum(score) as sum_sco FROM score GROUP BY id) t2 ON t1.id = t2.id ORDER BY clazz DESC, sum_sco DESC。
这个子查询首先从 student t1 表中选择所有字段,并与子查询 SELECT id,sum(score) as sum_sco FROM score GROUP BY id 进行连接。子查询的目的是计算每个学生的总分数 sum_sco。连接条件是 t1.id = t2.id,即 student 表的 id 字段与 score 表的 id 字段相等。
然后,查询结果按照 clazz 和 sum_sco 的降序进行排序。
@last_clazz:用于保存上一个班级的值,以便与当前班级进行比较。
IF(clazz = @last_clazz, @i := @i + 1, @i := 1) as rank:根据当前班级是否与上一个班级相同,来更新排名。如果相同,则将 @i 的值加 1,表示同一班级中的下一个学生。如果不同,则将 @i 的值重置为 1,表示进入了新的班级。
@last_clazz := clazz:将当前班级的值保存为 @last_clazz,以便在下一行计算排名时使用。
3、考虑同分排序
同样在上述代码的基础下再次对其进行操作:
set @i:=0; -- 记录正常顺序
set @last_clazz:=''; -- 记录是否同班级
set @last_sco:=0; -- 记录是否同分
SELECT
* -- 此时可以打印输出想要的字段,然后重新使用select对这个表进行处理,取出想要的数据
,@last_sco
,@last_clazz
,IF(clazz=@last_clazz,IF(sum_sco=@last_sco,@i,@i:=@i+1),@i:=1) as rank
-- 在对每个班排好名的情况下,再额外对其进行判断,如果分数相同,当前排名不动,反之继续增加排名
,@last_clazz:=clazz
,@last_sco:=sum_sco
FROM
(SELECT -- 合并两表,取出需要的字段
t1.*
,t2.sum_sco
FROM student t1
JOIN (SELECT id,sum(score)as sum_sco FROM score GROUP BY id) t2
ON t1.id = t2.id
ORDER BY clazz DESC,sum_sco DESC) tt1
运行结果如下所示,即同分排名一致,下列继续顺序排名
如果想要跳过同分这个排名,可以额外再定义一个变量,用来存放分数排名,如果分数一致,相同分数的排名同学一致,后面跳过当前顺序的排名,例如下图排名rank
简单修改代码为下列情况:
标签:sco,12,last,--,sum,clazz,SQL,写法,id From: https://blog.csdn.net/qq_64603703/article/details/141186170