我很长时间以来一直在与以下代码作斗争。我是PHP新手。mysql的这段代码100%在phpmyadmin中工作。我正在使用<?php echo $row['Rank'];?>，但我只为所有学生获得1。但是sql代码运行良好。我在这里做了sql小提琴http://sqlfiddle.com/#！2/24855/1请帮帮我。
Select Distinct regd, Roll_no, Name_of_Student, Test_date,
English, f_eng, Mizo, f_mz, Hindi, f_hn, Mathematics, f_maths, SS, f_ss, Science,
f_sc, score, fmscore, perc, Rank FROM (SELECT *, IF(@marks = (@marks := score), @auto,
@auto := @auto + 1) AS Rank FROM
(SELECT regd, Roll_no, Name_of_Student, Test_date,
SUM( IF( `Subject` = 'English', Mark_score, 0 ) ) AS English,
SUM( IF( `Subject` = 'English', Full_mark, 0 ) ) AS f_eng,
SUM( IF( `Subject` = 'Mizo', Mark_score, 0 ) ) AS Mizo,
SUM( IF( `Subject` = 'Mizo', Full_mark, 0 ) ) AS f_mz,
SUM( IF( `Subject` = 'Hindi', Mark_score, 0 ) ) AS Hindi,
SUM( IF( `Subject` = 'Hindi', Full_mark, 0 ) ) AS f_hn,
SUM( IF( `Subject` = 'Mathematics', Mark_score, 0 ) ) AS Mathematics,
SUM( IF( `Subject` = 'Mathematics', Full_mark, 0 ) ) AS f_maths,
SUM( IF( `Subject` = 'SS', Mark_score, 0 ) ) AS SS,
SUM( IF( `Subject` = 'SS', Full_mark, 0 ) ) AS f_ss,
SUM( IF( `Subject` = 'Science', Mark_score, 0 ) ) AS Science,
SUM( IF( `Subject` = 'Science', Full_mark, 0 ) ) AS f_sc,
SUM(Full_mark) AS fmscore,
SUM(Mark_score) AS score, SUM(Mark_score)/SUM(Full_mark)*100 as perc FROM cxexam,
(SELECT @auto := 0, @marks := 0) AS init GROUP BY regd ORDER BY score DESC) t) AS result where Test_date between '2013-07-01' and '2013-07-31'
以下是PHP部分。看起来还可以。除了没有正确输出等级之外，它还正确地响应了所有内容。在排名行中，它只显示1作为所有学生的排名。你可以从下面的代码中理解我试图实现的目标。我真的需要你的帮助。我知道你们对这个问题很熟悉。
<table width="800" border="1" class="tablestyle" cellpadding="8" cellspacing="6">
<tr>
<th align="center" width="80">Roll No</th>
<th align="center" width="100">Name_of_Student</th>
<th align="center" width="40">English</th>
<th align="center" width="55">Mizo</th>
<th align="center" width="55">Hindi</th>
<th align="center" width="55">Math</th>
<th align="center" width="70">SS</th>
<th align="center" width="40">Science</th>
<th align="center" width="70">FM</th>
<th align="center" width="70">MO</th>
<th align="center" width="40">Percentage</th>
<th align="center" width="40">Rank</th>
<th align="center" width="40">Result</th>
</tr>
<?php
while($row=mysql_fetch_array($res))
{
?>
<tr>
<td align="center"><?php echo $row['Roll_no'];?></td>
<td align="left"><?php echo $row['Name_of_Student'];?></td>
<td align="center"><?php echo $row['English'];?></td>
<td align="center"><?php echo $row['Mizo'];?></td>
<td align="center"><?php echo $row['Hindi'];?></td>
<td align="center"><?php echo $row['Mathematics'];?></td>
<td align="center"><?php echo $row['SS'];?></td>
<td align="center"><?php echo $row['Science'];?></td>
<td align="center"><?php echo $row['fmscore'];?></td>
<td align="center"><?php echo $row['score'];?></td>
<td align="center"><?php echo number_format($row['perc'],0);?>%</td>
<td><?php echo $row['Rank']; ?></td>
<td><?php
if ($row['English']/$row['f_eng']*100>=40 && $row['Mizo']/$row['f_mz']*100>=40 && $row['Hindi']/$row['f_hn']*100>=40 && $row['Mathematics']/$row['f_math']*100>=40 && $row['SS']/$row['f_ss']*100>=40 && $row['Science']/$row['f_sc']*100>=40)
{
echo "<font color=green>Passed</font>";
}
else
{
echo "<font color=red>Failed</font>";
}
?></td>
</tr>
<?php
}
?>
</table>
不工作的零件：rank和result。感谢您的帮助。
您的sql代码有几个问题（例如，永远不要混合使用DISTINCT和GROUP BY，在内部选择中使用WHERE等）。话虽如此，你的查询应该看起来像
SELECT regd, Roll_no, Name_of_Student, Test_date,
English, f_eng,
Mizo, f_mz,
Hindi, f_hn,
Mathematics, f_maths,
SS, f_ss,
Science, f_sc,
score, fmscore, perc, Rank
FROM
(
SELECT t.*, IF(@p = score, @n, @n := @n + 1) AS Rank, @p := score
FROM
(
SELECT regd, Roll_no, Name_of_Student, Test_date,
SUM(IF(Subject = 'English' , Mark_score, 0)) English,
SUM(IF(Subject = 'English' , Full_mark, 0)) f_eng,
SUM(IF(Subject = 'Mizo' , Mark_score, 0)) Mizo,
SUM(IF(Subject = 'Mizo' , Full_mark, 0)) f_mz,
SUM(IF(Subject = 'Hindi' , Mark_score, 0)) Hindi,
SUM(IF(Subject = 'Hindi' , Full_mark, 0)) f_hn,
SUM(IF(Subject = 'Mathematics', Mark_score, 0)) Mathematics,
SUM(IF(Subject = 'Mathematics', Full_mark, 0)) f_maths,
SUM(IF(Subject = 'SS' , Mark_score, 0)) SS,
SUM(IF(Subject = 'SS' , Full_mark, 0)) f_ss,
SUM(IF(Subject = 'Science' , Mark_score, 0)) Science,
SUM(IF(Subject = 'Science' , Full_mark, 0)) f_sc,
SUM(Full_mark) fmscore,
SUM(Mark_score) score,
SUM(Mark_score) / SUM(Full_mark) * 100 perc
FROM cxexam, (SELECT @n := 0, @p := 0) n
WHERE Test_date BETWEEN '2013-07-01' AND '2013-07-31'
GROUP BY regd
ORDER BY score DESC
) t
) r
这是SQLFiddle演示
现在php代码
$link = mysql_connect('localhost', 'user', 'password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db('dbname', $link);
if (!$db_selected) {
die ('Can''t use db : ' . mysql_error());
}
$sql = "
SELECT regd, Roll_no, Name_of_Student, Test_date,
English, f_eng,
Mizo, f_mz,
Hindi, f_hn,
Mathematics, f_maths,
SS, f_ss,
Science, f_sc,
score, fmscore, perc, Rank
FROM
(
SELECT t.*, IF(@p = score, @n, @n := @n + 1) AS Rank, @p := score
FROM
(
SELECT regd, Roll_no, Name_of_Student, Test_date,
SUM(IF(Subject = 'English' , Mark_score, 0)) English,
SUM(IF(Subject = 'English' , Full_mark, 0)) f_eng,
SUM(IF(Subject = 'Mizo' , Mark_score, 0)) Mizo,
SUM(IF(Subject = 'Mizo' , Full_mark, 0)) f_mz,
SUM(IF(Subject = 'Hindi' , Mark_score, 0)) Hindi,
SUM(IF(Subject = 'Hindi' , Full_mark, 0)) f_hn,
SUM(IF(Subject = 'Mathematics', Mark_score, 0)) Mathematics,
SUM(IF(Subject = 'Mathematics', Full_mark, 0)) f_maths,
SUM(IF(Subject = 'SS' , Mark_score, 0)) SS,
SUM(IF(Subject = 'SS' , Full_mark, 0)) f_ss,
SUM(IF(Subject = 'Science' , Mark_score, 0)) Science,
SUM(IF(Subject = 'Science' , Full_mark, 0)) f_sc,
SUM(Full_mark) fmscore,
SUM(Mark_score) score,
SUM(Mark_score) / SUM(Full_mark) * 100 perc
FROM cxexam, (SELECT @n := 0, @p := 0) n
WHERE Test_date BETWEEN '2013-07-01' AND '2013-07-31'
GROUP BY regd
ORDER BY score DESC
) t
) r";
$result = mysql_query($sql);
if(!$result) {
die(mysql_error()); // TODO: better error handling
}
while($row = mysql_fetch_assoc($result)) {
echo "{$row['regd']} - {$row['Rank']}<br>";
}
输出（如预期）：
40-12-23-220-3
对于排名部分，这是100%有效的。看来你对php不是很了解。
<?php
$rank = $prevScore = 0;
{
$count++; // always increment
if ($row_dep['perc'] > $prevScore) {
//whenever a non-tie occurs the my rank catches up
$my_rank = $count;
$rank = $count;
} else {
//whenever a tie occurs, just use the my rank
$rank = $my_rank;
}
$prevScore = $row_dep['perc'];
echo $rank;
}
?>
问题出在这部分代码中：-SELECT*，IF（@marks=（@marks:=score），@auto，@auto:=@auto+1）你需要确保逻辑是正确的。