这是我的表
+------+-------+--------+
| 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>
尝试以下查询：
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
在这里你可以看到小提琴：
新小提琴：http://sqlfiddle.com/#!2/d3635/28