mysql - 如何使用单个 sql 查询更新多行？(使用 "order by")

Question

我这里有这个用户表:(我想根据分数更新排名)

这是 SQL fiddle :

http://sqlfiddle.com/#!9/2f459d/1/0

还有代码审查:

create table users2 (  
     ranking int ,
     user_id int auto_increment primary key, 
     user_n varchar(70) charset utf8mb4 not null comment 'username',
     score int default '0' null, 
     constraint username  
     unique (user_n)  );

有值(value)观:

INSERT INTO users2 (user_id, user_n, score)
value(1,'mohamad',50),
     (2,'john',100),
     (3,'nik',150),
     (4,'sara',200),
     (5,'tom',250);

我想根据分数更新排名。我可以选择但无法更新:

SELECT c.u_rank,a.user_id,a.user_n,score from users2 a LEFT JOIN (SELECT @s:=@s+1 u_rank,user_id
FROM users2 d,(SELECT @s:=0) as b order by d.score DESC )  AS c
ON a.user_id = c.user_id order by score DESC

到目前为止我的努力:

UPDATE users2 a LEFT JOIN (SELECT @s:=@s+1 u_rank,user_id
 FROM users2 ,(SELECT @s:=0) as b ORDER BY score DESC )  AS c
ON a.user_id = c.user_id  SET a.ranking = c.u_rank

Answer 1

在 MySQL 中，您可以通过首先设置变量然后在 UPDATE 中使用 ORDER BY 来使用变量:

SET @s := 0;

UPDATE users2 u
    SET u.ranking = (@s := @s + 1)
    ORDER BY score DESC ;

Here是一个 SQL fiddle 。