MySQL更新语句存储排名位置 [英] MySQL update statement to store ranking positions

问题描述

我正在设法解决一个查询，但我只是想不通.如果有人给我一个指针，我将不胜感激.作为我要实现的目标的简单示例，我在数据库中有这些记录

I'm trying to get my head around a query and I just can't figure it out. I would appreciate if someone give me a pointer. As a simple example of what I'm trying to achieve, I have these records in the database

Score|Ranking
-------------
100  |0
200  |0
300  |0

我希望排名字段根据得分最高的人包含1,2,3，因此结果应为:

And I would like the Ranking field to contain 1,2,3 based on who's got the highest score so the result should be:

Score|Ranking
-------------
100  |3
200  |2
300  |1

此刻，我正在为所有这些记录做一个for next循环，但考虑到实际上这可能是几千个-可能要花很长时间！有没有人对魔术查询有一个主意，可以一口气做到这一点?

At the moment, I'm doing a for next loop for all these records but given that in reality that could be a few thousand - that could take forever! Does anyone have an idea on a magic query which would do this in one go?

推荐答案

在MySQL中，您可以使用row_number .

In MySQL, you can use row_number.

这是一个示例在SELECT中使用它的方法:

select @rownum:=@rownum+1 ‘rank’, p.* 
from player p, (SELECT @rownum:=0) r 
order by score desc;

如果您使用INSERT INTO这样的INSERT INTO，您将获得排名.

If you INSERT INTO using a SELECT like this, you will get your rankings.

这篇关于MySQL更新语句存储排名位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！