从 PHP 中的总和打印排名 &数据库 [英] Print Rank from sum in PHP & MySql

问题描述

我的 Sql 数据库表如下

I have My Sql Database Table as below

 idno      Name        Subject         Score 

  1        Mahesh      English           55
  1        Mahesh      Maths             25
  1        Mahesh      Science           35
  2        Richards    English           65 
  2
  2 
  3
  3         
  3
  .................. Like ways so on till id number 12000

现在我将为用户提供一个表单，并告诉他们输入 ID 号并提交，然后输出应该是.

Now i will provide a form for the user and tell them to enter id number and submit then the output should be.

如果用户输入 idno : 3 并提交表单，那么输出应该是

If User Enters idno : 3 and submit the form then the output should be

   IDNO        NAME         TOTAL SCORE       RANK
     1         MAHESH           95            2546 (Example)

我在这里使用这个代码

   $id = mysql_real_escape_string($_POST['id']);
   $sum = "SELECT idno, SUM(score) AS tech
   FROM jbit 
   WHERE htno='$id'";
   $result1 = mysql_query($sum);
   echo "
   <center><table id='mytable' cellspacing='0'  border=3 align=center>
   <tr>
   <TH scope='col'>IDNO</TH>
   <TH scope='col'>NAME</TH>
   <TH scope='col'>TOTAL SCORE</TH>
   <TH scope='col'>RANK</TH>
   </tr><center>";
   while ($row = mysql_fetch_assoc($result1)){
echo "<tr>";
   echo "<td align=center>" . $row['idno']. "</td>";
   echo "<td align=center>" . $row['name']. "</td>";
   echo "<td align=center>" . $row['tech']. "</td>";
   echo "</tr>";

这里我无法计算排名并打印排名，我该怎么做?

Here I am unable to calculate the rank and print the rank, how can I do this?

基于总分，即 SUM(Score) 作为技术排名应计算 &印刷

Based on Total Score i.e. SUM(Score) as Tech Rank shold be calculated & Printed

推荐答案

在您的问题上投入了一点时间后，我终于创建并测试了以下 SQL 查询，它产生与您要求的相同的结果，并且处理好关系.

After investing a little bit of time on your issue, I have finally created and tested the following SQL query, it produces the same result as you requested and it also handles the ties very well.

SELECT idno, name, rank,total_score
FROM (SELECT *,  IF(@marks=(@marks:=total_score), @auto, @auto:=@auto+1) AS rank 
FROM (SELECT * FROM 
  (SELECT idno, name, SUM(score) AS total_score 
    FROM jbit, 
    (SELECT @auto:=0, @marks:=0) as init 
     GROUP BY name) sub ORDER BY total_score DESC)t) as result
WHERE idno ='1'

希望这会有所帮助.

这篇关于从 PHP 中的总和打印排名 &数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！