php - 求和 php 中的行和排名

Question

我正在用 php 创建一个学生信息系统，数据库如下所示

**studentId** **Math** **English** **Science** **className**
1                 80        66         85       3p1
2                 75        83         84       3p1
3                 70        88         90       3p1
4                 50        82         50       5p3
5                 88        77         77       3p1
6                 92        97         96       3p1

我只想对使用 php 的 className 3P1 的成员进行排名。当包含以下内容时，下面的 sql 语句将在 phpmyadmin 中正常排名

SET @myclassName:=0;
SET @myRank:= 0;
SET @myNextRank:= 0;
SET @myTotals:= 0;   

但是，当我将其转换为 php 并在 php 中使用时，生成的排名不一致。每次我重新加载页面时，排名也会发生变化。我如何包含

SET @myclassName:=0;
SET @myRank:= 0;
SET @myNextRank:= 0;
SET @myTotals:= 0;

在 php 查询中。

<?php 

$StudentMarkz = "SELECT X.*,
                        @myRank:=CASE WHEN @myclassName <> className
                                           THEN @myNextRank:=1
                                      WHEN @myTotals <> totals
                                           THEN @myRank:=@myNextRank
                                      ELSE @myRank
                                 END AS Rank,
                        @myclassName:=className AS myclassName,
                        @myTotals:=totals AS myTotals,
                        @myNextRank:=@myNextRank+1 AS myNextRank
                   FROM ( SELECT studentmarkstable.studentId,surname, firstName,
                                 Math,
                                 English,
                                 Science,
                                 SocialStudies,
                                 studentmarkstable.className,
                                 Math + English + Science + SocialStudies AS totals
                            FROM studentmarkstable, student 
                           WHERE student.studentId=studentmarkstable.studentId 
                             AND studentmarkstable.className='3P1' 
                           ORDER BY className ASC,
                                    6 DESC
                        ) X";
$marksQuery = mysql_query($StudentMarkz) or die (mysql_error());

?>

如果我使用 $marksQuery = mysqli_multi_query($StudentMarkz) 或死掉 (mysql_error());我得到一个错误。

提前谢谢你

Answer 1

好的。这是 MySQL 的解决方案，因为它可能是 PHP 脚本编写者中最常用的数据库:

SET @myClass:=0;
SET @myRank:= 0;
SET @myTotals:= 0;
SELECT X.*,
        @myRank:=CASE WHEN @myClass <> class 
                           THEN 1
                      WHEN @myTotals <> totals 
                           THEN @myRank+1
                      ELSE @myRank
                 END AS Rank,
        @myClass:=class AS myClass,
        @myTotals:=totals AS myTotals
  FROM ( SELECT studentId,
                Math,
                English,
                Science,
                Class,
                Math + English + Science AS totals
           FROM studentGrades
          ORDER BY 5 ASC,
                   6 DESC
       ) X

给出:

studentId  Math  English  Science  Class  totals  Rank  myClass  myTotals
2          75    83       84       3p1    242     1     3p1      242
5          88    77       77       3p1    242     1     3p1      242
1          80    66       85       3p1    231     2     3p1      231
6          92    97       96       5p2    285     1     5p2      285
3          70    88       90       5p2    248     2     5p2      248
4          50    82       50       5p2    182     3     5p2      182

队伍没有差距

编辑

如果您希望在多个具有相同排名的学生之后保持后续学生排名位置(例如 1,2,2,4 而不是 1,2,2,3)，请使用以下内容:

SET @myClass:=0;
SET @myRank:= 0;
SET @myNextRank:= 0;
SET @myTotals:= 0;
SELECT X.*,
        @myRank:=CASE WHEN @myClass <> class 
                           THEN @myNextRank:=1
                      WHEN @myTotals <> totals 
                           THEN @myRank:=@myNextRank
                      ELSE @myRank
                 END AS Rank,
        @myClass:=class AS myClass,
        @myTotals:=totals AS myTotals,
        @myNextRank:=@myNextRank+1 AS myNextRank
  FROM ( SELECT studentId,
                Math,
                English,
                Science,
                Class,
                Math + English + Science AS totals
           FROM studentGrades
          ORDER BY 5 ASC,
                   6 DESC
       ) X

给予

studentId  Math  English  Science  Class  totals  Rank  myClass  myTotals  myNextRank
2          75    83       84       3p1    242     1     3p1      242       2
5          88    77       77       3p1    242     1     3p1      242       3
1          80    66       85       3p1    231     3     3p1      231       4
6          92    97       96       5p2    285     1     5p2      285       2
3          70    88       90       5p2    248     2     5p2      248       3
4          50    82       50       5p2    182     3     5p2      182       4

队伍中有差距