如何使用MySQL Query和PHP分发排名

Question

I have to distribute ranking to the 5000 students in an exam. Ranking is based on the score and the time taken (in seconds) to obtain that score.

For example is 5 students have same score, then taken will be the criteria to calculate their ranks otherwise score should be the criteria to calculate their ranks.

Following is my table tbRank

ID  StudID  Score Time Date        Rank
1   11      8     60   09-11-2013 
2   22      6     45   09-11-2013
3   33      4     76   09-11-2013
4   44      6     67   09-11-2013
5   55      8     35   09-11-2013 
6   66      8     35   08-11-2013
7   77      8     39   08-11-2013

Now rank column in above table should be updated as:

ID  StudID  Score Time Date        Rank
1   11      8     60   09-11-2013  2
2   22      6     45   09-11-2013  3
3   33      4     76   09-11-2013  5
4   44      6     67   09-11-2013  4
5   55      8     35   09-11-2013  1
6   66      8     35   08-11-2013  1
7   77      8     39   08-11-2013  2

I want to make a MySQL Query to do this business. Similarly there can be more than 10000 records in the table. So I need an optimized query for this functionality.

Note: I am using PHP and MYSQL.

Update: Everyday almost 5000 new entries will be created in the table and after all insertions are made, rank column will be updated once in a day. Now please suggest me the best way to do this. If I update rank column in the table, then only once I will have to do it, otherwise everytime while fetching the rank of the student, I will have to make calculations.

Answer 1

If you want to update the table you can do:

UPDATE tbRank t
INNER JOIN (
  SELECT ID, StudID, Score, `Time`, @rownum := @rownum + 1 AS rank
  FROM (
    SELECT ID, StudID, Score, `Time`
    FROM tbRank
    WHERE date = date(now())
    ORDER BY score DESC, `time` ASC
    ) a
  JOIN (
    SELECT @rownum := 0
    ) r
  ) b ON b.id = t.id
SET t.rank = b.rank

But since you would do this every day, you would have to update all rows of rank column to null before executing this.

I wouldn't advise this approach because if you change one of them or insert a new record, you have to do the update again.

You can simply use this query whenever you want to get the ranking:

SELECT ID, StudID, Score, `Time`, @rownum := @rownum + 1 AS rank
  FROM (
    SELECT ID, StudID, Score, `Time`
    FROM tbRank
    WHERE date = date(now())
    ORDER BY score DESC, `time` ASC
    ) a
  JOIN (
    SELECT @rownum := 0
    ) r

Additionally, if you want to know the rank of a specific student, you can do the following query:

SELECT * FROM 
(
  SELECT ID, StudID, Score, `Time`, @rownum := @rownum + 1 AS rank
  FROM (
    SELECT ID, StudID, Score, `Time`
    FROM tbRank
    WHERE date = date(now())
    ORDER BY score DESC, `time` ASC
    ) a
  JOIN (
    SELECT @rownum := 0
    ) r
  ) b
WHERE b.StudID = 11;

<kbd>sqlfiddle demo</kbd>