我必须将排名分配给考试中的 5000 名学生。排名基于分数和获得该分数所需的时间(以秒为单位)。
例如,5名学生的分数相同,那么取将是计算其排名的标准,否则分数应是计算其排名的标准。
以下是我的表格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
现在,上表中rank column
应更新为:
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
我想做一个MySQL查询来做这个业务。同样,表中可以有超过 10000 条记录。所以我需要对此功能进行优化查询。
注意:我正在使用PHP和MYSQL。
Update:
每天将在表中创建近 5000 个新条目,并且在进行所有插入后,排名列将每天更新一次。现在请向我建议最好的方法。如果我更新表中的排名列,那么我只需要这样做一次,否则每次在获取学生的排名时,我都必须进行计算。
如果要
更新表,可以执行以下操作:
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
但是由于您每天都会这样做,因此您必须在执行此操作之前将排名列的所有行更新为 null。
我不建议使用这种方法,因为如果您更改其中一个或插入新记录,则必须再次进行更新。
每当您想要获得排名时,您只需使用此查询:
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
此外,如果您想知道特定学生的排名,您可以执行以下查询:
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;
SQLfiddle 演示
所以,这是你的表:
ID StudID Score Time
1 11 8 60
2 22 6 45
3 33 4 76
4 44 6 67
5 55 8 35
这是构建Rank
的查询:
SELECT * FROM tbRank GROUP BY Score DESC, Time ASC
它将"重新排列"得分最高的表格行,并且时间最快,将从上到下排列