如何改进这个php mysql代码? [英] How to improve this php mysql code?

问题描述

通过这些代码，我回显了一个学生的 Rank，其 regd 等于 $regd.事实上，这是一个工作代码.但是，朋友建议我在mysql语句中Distinct和Group By不应该一起使用.但是作为一个新手，我不知道如何在不使用 Distinct 的情况下实现它，因为它不会返回没有 Distinct 的行.谁能建议我如何改进这些代码?

With these codes I echo Rank of a student whose regd is equal to $regd. In fact, this is a working code. However, I was advised by a friend that in mysql statement Distinct and Group By should not be used together. But as a newbie, I could not figure out how I would implement it without using Distinct because it does not return rows without Distinct. Can anyone suggest me how I improve these codes?

<?php 
mysql_select_db($database_dbconnect, $dbconnect);
$query_myrank = "SELECT Distinct regd, Name_of_exam, 
                        Name_of_Student, TOTALSCORE, Rank 
                 FROM (SELECT *, IF(@marks = (@marks := TOTALSCORE), 
                              @auto, @auto := @auto + 1) AS Rank 
                       FROM (SELECT Name_of_Student, regd, 
                                   Name_of_exam, SUM(Mark_score) AS TOTALSCORE 
                             FROM cixexam, (SELECT @auto := 0, 
                                                   @marks := 0) AS init 
                                            GROUP BY regd 
                             ORDER BY TOTALSCORE DESC) t) AS result 
                 HAVING (Name_of_exam='First Terminal Exam' OR 
                         Name_of_exam='First Term Test')";

$myrank = mysql_query($query_myrank, $dbconnect) or die(mysql_error());

$i = 0;
$j = 0;
$data = array();
while($row_myrank = mysql_fetch_assoc($myrank))
{
    $data[$i] = $row_myrank;
    if(isset($data[$i - 1]) 
           && $data[$i - 1]['TOTALSCORE'] == $data[$i]['TOTALSCORE'])
    {
       $data[$i]['Rank'] = $j;
    }else{
       $data[$i]['Rank'] = ++$j;
    }
       $i++;
}
foreach($data as $key => $value)
{
    if($value['regd'] == $regd)
    {
        echo $value['Rank'];
    }
}
?>    

推荐答案

Distinct 比 Group By 慢.您可以在不使用 Group By 和 Distinct 的情况下这样做，这可能就是您想要实现的目标.

Distinct is slower than Group By. You can go like this without using Group By and Distinct together and this could be what you want to achieve.

SELECT regd, Roll_no, Name_of_Student, Name_of_exam,
       TOTALSCORE, Rank 
FROM 
(
  SELECT t.*, IF(@p = TOTALSCORE, @n, @n := @n + 1) AS Rank, @p := TOTALSCORE 
    FROM
  (
    SELECT regd, Roll_no, Name_of_Student, Name_of_exam, 
            SUM(Mark_score) TOTALSCORE
    FROM cixexam, (SELECT @n := 0, @p := 0) n 
     WHERE (Name_of_exam='First Terminal Exam' OR Name_of_exam='First Term Test')
     GROUP BY regd 
     ORDER BY TOTALSCORE DESC
  ) t
) r

这篇关于如何改进这个php mysql代码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！