php - 使用 while 循环对 PHP MYSQL 进行排名

Question

我有一个问题。我正在根据申请人的考试成绩制定一个排名系统。这是代码:

$testScore = "SELECT Overall_Score, First_Name
                      FROM applicant_details, person, person_details
                      where person.ID_No like person_details.ID_No and Position_Applied = 'Work137' and Person_Type = 'Applicant' and applicant_details.ID_No like person.ID_No";

$result = mysql_query($testScore);

$rank = 0;
$lastScore = false;
$rows = 0;

while( $row = mysql_fetch_array( $result ) ){               

       $name = $row['First_Name'];
       $overall = $row['Overall_Score'];
       $rows++;

       if( $lastScore != $overall){
           $lastScore = $overall;
           $rank = $rows;
        }
        echo"Name:$name rank:$rank score: $overall </br>";
}

这段代码的输出是，谁第一个查询的就是1号。输出示例:

姓名:Utaha 排名:1 得分:85

姓名:Rikka 排名:2 得分:90

我想要一个输出:

姓名:Utaha 排名:2 得分:85

姓名:Rikka 排名 1 得分:90

Answer 1

只需使用 ORDER BY [要排序的列名] [DESC/ASC] 按值排序...

SELECT Overall_Score, First_Name
                  FROM applicant_details, person, person_details
                  where person.ID_No like person_details.ID_No and Position_Applied = 'Work137' and Person_Type = 'Applicant' and applicant_details.ID_No like person.ID_No ORDER BY Overall_Score DESC

如果你想保持顺序，那么应该使用 mysql_num_rows() 来获取答案的总数，并使用该数字对它们进行排名:

$rows = mysql_num_rows($result);
while( $row = mysql_fetch_array( $result ) ){               

   $name = $row['First_Name'];
   $overall = $row['Overall_Score'];

   if( $lastScore != $overall){
       $lastScore = $overall;
       $rank = $rows;
    }
    echo"Name:$name rank:$rank score: $overall </br>";
   $rows--;

}