mysql - MySQL 排序与分组
问题描述
我是新手,试图对不同班级的学生评分进行排名,我的查询如下:
SELECT Branch, Grade, Section, RollNo, FIrst_Name, Father_Name, Grand_Father_Name, AverageMark, rank
FROM (
SELECT Branch, Grade, Section, RollNo, FIrst_Name, Father_Name, Grand_Father_Name, AverageMark, @curRank := IF(@prevRank = AverageMark, @curRank, @incRank) AS rank, @incRank := @incRank + 1, @prevRank := AverageMark
FROM studentsaverage p,
( SELECT @curRank :=0, @prevRank := NULL, @incRank := 1 ) r
ORDER BY AverageMark DESC
) s
突出显示的学生排名是 4 而不是 1,因为学生来自另一所学校,我该如何解决这个问题?
解决方案
实际上我没有什么可以测试我的代码,但是您的代码的问题是您没有验证分支是否相同:
SELECT Branch,
Grade,
SECTION,
RollNo,
FIrst_Name,
Father_Name,
Grand_Father_Name,
AverageMark,
rank
FROM
( SELECT Branch,
Grade,
SECTION,
RollNo,
FIrst_Name,
Father_Name,
Grand_Father_Name,
AverageMark,
@curRank := IF(@curBranch = Branch ,IF(@prevRank = AverageMark, @curRank, @incRank),1) AS rank,
@incRank := @incRank + 1,
@prevRank := AverageMark,
@curBranch = Branch
FROM studentsaverage p,
(SELECT @curRank :=0, @prevRank := NULL, @incRank := 1, @curBranch := NULL) r
ORDER BY Branch, AverageMark DESC) s
推荐阅读
- firebase - Firestore 文档中的附加 id 字段
- java - indexOf 返回字符串中的子字符串索引
- postgresql - 如何为我的函数的每次执行在 pg_log 目录中创建一个日志文件
- postgresql - 在 AWS RDS Postgres 上,什么可能导致磁盘延迟上升而 iOP/吞吐量下降?
- nginx - NGINX 突然停止代理请求
- javascript - 发布 JSON 时出现 ValidationError
- javascript - 意思是堆栈帮助:TypeError:汽车不是函数
- android - 模拟器每次使用都会变慢
- r - 在一个热编码中每行有多个值 - 是否推荐这样做?
- javascript - 在 AngularJS 登录页面上自动填充 UserId