如何根据 MySql Server 中的最高分和最低分计算排名 [英] How can I calculate rank based on highest Score and lowest Minutes in MySql Server

问题描述

我的表结构是

CREATE TABLE IF NOT EXISTS `tbl_user_result` (
   `Id` int(11) NOT NULL AUTO_INCREMENT,
   `ResultId`int(11),
   `PaperId` int(11) ,
   `Title` varchar(45),
   `TotalQuestions` int(11) ,
   `Attempt` int(11) ,
   `Correct` int(11) ,
   `Wrong` int(11) ,
   `Score` int(45) ,
   `Minutes` int(11) ,
   `TimeSt` varchar(45) ,
   `UserEmail` varchar(45) ,
   `UserName` varchar(45) ,
  PRIMARY KEY (`Id`));

我尝试了以下 SQL 查询

I tried following SQL query

SELECT 
    id,
    PaperId,
    Title,
    Score,
    Minutes,
    (SELECT 
            COUNT(*) + 1
        FROM
            tbl_user_result 
        WHERE
            Score > x.Score AND Minutes > x.Minutes) AS Rank
FROM
    `tbl_user_result` as x

推荐答案

最简单的方法是使用变量:

The easiest way is to use variables:

select r.*
from (select r.*, (@rn := @rn + 1) as rank
      from tbl_user_result r cross join (select @rn := 0) vars
      order by score desc, minutes asc
     ) r;

如果您没有重复项，这有效.

This works if you have no duplicates.

我认为以下是您想要的查询版本:

I think the following is the version of your query that you want:

SELECT id, PaperId, Title, Score, Minutes,
       (SELECT COUNT(*) + 1
        FROM tbl_user_result r2
        WHERE r2.Score > r.Score OR
              (r2.Score = r.Score AND r2.Minutes < r.Minutes)
       ) AS Rank
FROM  tbl_user_result r;

这篇关于如何根据 MySql Server 中的最高分和最低分计算排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！