php - 组、积分总和、组

Question

我的 table

+----+-------+------+---------+--------+------+
| ID | CLASS | NAME | SCHOOL  | POINTS | YEAR |
+----+-------+------+---------+--------+------+
|  1 |     5 | S1   | School1 |      5 | 2013 |
|  2 |     6 | S2   | School1 |      0 | 2013 |
|  3 |     5 | S3   | School2 |      1 | 2014 |
|  4 |     6 | S4   | School1 |      3 | 2013 |
|  5 |     6 | S5   | School2 |      1 | 2014 |
|  6 |     5 | S6   | School1 |      0 | 2013 |
|  7 |     6 | S7   | School2 |      3 | 2013 |
|  8 |     6 | S8   | School1 |      5 | 2013 |
|  9 |     5 | S9   | School1 |      1 | 2014 |
| 10 |     5 | S10  | School1 |      0 | 2013 |
| 11 |     6 | S11  | School2 |      5 | 2014 |
| 12 |     5 | S12  | School1 |      1 | 2013 |
+----+-------+------+---------+--------+------+

在这里，我想找到每所学校的总分，按类(class)和年级按最高分排序。

这是我的问题，我想显示 5 级，6 分也在总分之内。

<?php
$sql="SELECT Class, School, SUM(Points) FROM students WHERE Year='2013'
GROUP BY Class,School ORDER BY SUM(Points)";

$result=mysql_query($sql);
$count=1;
while ($row = mysql_fetch_array($result)) 
{   
?>
    <table>
     <tr>
        <td><?php echo  $row['School'];?></td>
        <td><?php echo  $row["SUM(Points)"];?></td>
     </tr>

      <tr>
         <td>Class <?php echo  $row['Class'];?></td>
         <td><?php echo $row['Points'];?></td>
      </tr>
     </table>
    <?php
      }
    ?>

所以我的最终输出看起来:

| School1 |    14 |
--------------------
  Class 5       6
  Class 6       8
--------------------

| School2 |     3 |
  Class 5       0
  Class 6       3
+------+------+------+

请帮助我。

Answer 1

分“两步”完成:

<?php
$sql="SELECT School, SUM(Points) FROM students WHERE Year='2013'
GROUP BY School ORDER BY SUM(Points)";

$result=mysql_query($sql);
$count=1;
while ($row = mysql_fetch_array($result)) 
{   
?>
    <table>
     <tr>
        <td><?php echo  $row['School'];?></td>
        <td><?php echo  $row["SUM(Points)"];?></td>
     </tr>

    <?php

      $sql2="SELECT Class, SUM(Points) FROM students WHERE Year='2013' and School = '" . $row['School'] . "'
      GROUP BY Class ORDER BY SUM(Points)";
      $result2=mysql_query($sql2);
      while ($row2 = mysql_fetch_array($result2)) 
      {  

      ?>
            <tr>
               <td>Class <?php echo  $row2['Class'];?></td>
               <td><?php echo $row2['SUM(Points)'];?></td>
            </tr>
      <?php
      }
      ?>


     </table>
    <?php
      }
    ?>