php - 找到所有分数并展示他们的奖品

Question

这是我的 table

+------+-------+--------+
| NAME | MARKS | SCHOOL |
+------+-------+--------+
| N1   |    80 | S1     |
| N2   |    90 | S2     |
| N3   |    75 | S1     |
| N4   |    70 | S2     |
| N5   |    55 | S1     |
| N6   |    75 | S1     |
| N7   |    70 | S1     |
| N8   |    80 | S1     |
| N9   |    60 | S1     |
+------+-------+--------+

在这里，我试图按分数降序显示每个学校学生的姓名和分数。此外，前三个最高分应显示为第一、第二、第三。

所以最终的输出看起来

+------+-------+--------+-------+
| NAME | MARKS | SCHOOL | PRIZE |
+------+-------+--------+-------+
| N1   |    80 | S1     | First |
| N8   |    80 | S1     | First |
| N3   |    75 | S1     | Second|
| N6   |    75 | S1     | Second|
| N7   |    70 | S1     | Third |
| N9   |    60 | S1     |       |
| N5   |    55 | S1     |       |
+------+-------+--------+-------+

我正在尝试使用以下代码，我尝试了三种不同的查询，但没有得到我的输出

<table width="100%" border="1">  
<tr>
<th>Name</th>
<th>Marks</th>
<th>Prize</th>
</tr>
<?php                     
$result = mysql_query("SELECT * FROM myTable WHERE SCHOOL= 'S1'
ORDER BY Marks Desc");

//$sql="SELECT * FROM myTable  AS t1  INNER JOIN (SELECT DISTINCT(Marks) 
AS best_marks FROM myTable WHERE SCHOOL= 'S1' ORDER BY Marks DESC LIMIT 3)
AS t2 ON t1.Marks = t2.best_marks ORDER BY Marks DESC, Name ASC";   


/* $sql = "SELECT * FROM myTable WHERE SCHOOL= 'S1' AND

 LEFT JOIN
 (select Marks from myTable order by Marks desc limit 1) AS max1
 ON myTable .Marks=max1.Marks
 LEFT JOIN
 (select Marks from myTable order by Marks desc limit 2,1) AS max2
 ON myTable .Marks=max2.Marks
 LEFT JOIN
 (select Marks from myTable order by Marks desc limit 3,1) AS max3
 ON myTable .Marks=max3.Marks;

 ORDER BY Marks Desc";
 */


while ($row = mysql_fetch_array($result)) 
{
?>
<tr>
<td><?php echo ucfirst($row['Name']);?></td>
<td><?php echo $row['Marks']; ?></td>

<td>

</td>
</tr> 
<?php   } ?>   
</table>

Answer 1

尝试这个查询:

select t.NAME, t.MARKS, t.SCHOOL,
       if(prize=1,'First', if(prize=2,'Second', if(prize=3,'Third',null))) as Prize
  from
    myTable t,
    (select m.marks, @prize := @prize + 1 as prize
      from (select distinct marks 
              from myTable 
             where SCHOOL= 'S1' 
              order by marks desc
            ) m,
            (select @prize := 0) p
    ) as mp
  where t.marks = mp.marks
    and t.SCHOOL= 'S1'
order by t.marks desc

在这里你可以在 fiddle 上看到:

新 fiddle : http://sqlfiddle.com/#!2/d3635/28