mysql - 按排名顺序设置字段，但在另一个字段的组中

Question

我是新来的，并且被 mysql 问题困扰。

我的表有以下文件:用户名 VARCHAR，状态 VARCHAR，得分 INT，排名 INT

我正在尝试根据用户分数设置排名字段，但按州单独设置。

我发现如果我忽略状态，我可以为他们所有人设置排名:

SET @pos := 0; UPDATE profiles SET rank = ( SELECT @pos := @pos + 1 ) ORDER BY cash DESC

我可以将其设置为单个状态，如下所示:

"SET @pos := 0; UPDATE profiles SET rank = ( SELECT @pos := @pos + 1 ) WHERE homeState = 'Arizona' ORDER BY cash DESC"

是否可以按州对它们进行分组，并在单个查询中按州设置它们的排名，而不是 50 个单独的查询？

谢谢!

Answer 1

您可以在select中执行此操作，如下所示:

select p.*,
       (@rn := if(@s = state, @rn + 1,
                  if(@s := state, 1, 1)
                 )
       ) as rn
from profiles p cross join
     (select @s := '', @rn := 0) params
order by state, score desc;

要将其放入更新中，您可以使用join:

update profiles p join
       (select p.*,
               (@rn := if(@s = state, @rn + 1,
                          if(@s := state, 1, 1)
                         )
               ) as rn
        from profiles p cross join
             (select @s := '', @rn := 0) params
        order by state, score desc
       ) pp
       on p.username = pp.username
    set p.rank = pp.rn;