MYSQL更新语句以按每个ID进行回填排名 [英] MYSQL update statement to backfill ranking by each id
本文介绍了MYSQL更新语句以按每个ID进行回填排名的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试实现一个查询,即针对每个用户ID对得分进行排名并回填排名字段,以便
I was trying to implement a query, that for each userid, rank the score and backfill the rank field, so that
id | score | rank
1 | 100 | 0
1 | 200 | 0
1 | 300 | 0
2 | 100 | 0
2 | 200 | 0
3 | 200 | 0
将成为
id | score | rank
1 | 100 | 3
1 | 200 | 2
1 | 300 | 1
2 | 100 | 2
2 | 200 | 1
3 | 200 | 1
我在这里看到了类似的问题 用于存储排名位置的MySQL更新语句
I saw a similar question here MySQL update statement to store ranking positions
但是,就我而言,如何为每个ID进行"ID分组"?
However, in my case, how can I do the 'group by id' for each id?
推荐答案
这可能不是最漂亮的方法,但是您可以轻松地执行以下操作:
It might not be the prettiest way, but you can easily do something like:
set @rank = 0;
set @prev = 0;
select id, score, IF (id = @prev, @rank := @rank + 1, @rank := 1), @prev := id
from scores
order by id, score;
我想您也想要update语句,那就是:
I guess you want the update statement as well, and that would be:
set @rank = 0;
set @prev = 0;
update scores
set rank = IF(id = @prev, @rank := @rank + 1, @rank := 1),
id = (@prev := id)
order by id, score;
这篇关于MYSQL更新语句以按每个ID进行回填排名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文