如何实现MySQL分组排名?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (258)

我试着给不同班级的学生进行排名:

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

扫码关注云+社区

领取腾讯云代金券