mysql根据id更新语句,MYSQL更新语句以按每个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

will become

id | score | rank

1 | 100 | 3

1 | 200 | 2

1 | 300 | 1

2 | 100 | 2

2 | 200 | 1

3 | 200 | 1

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;

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;