设置行的递增和重置值 [英] Setting an incrementing and resetting value for rows

问题描述

我要在现有表中添加新的 int 列。我正在尝试为表格中的所有行设置一个值。

I'm adding a new int column to an existing table. I'm trying to set a value for all the rows in the table.

players_table:
 - id
 - company_id
 - rank // new column

要设置等级的初始值，我只想按公司分组将等级增加1。

To set the initial values for rank I want to just increment the rank by 1 grouped by company.

示例

companies:
id | name
---------------
1  | first comp
2  | second comp

players:
id | company_id | rank 
-----------------------
1  |     1      |   1
2  |     1      |   2
3  |     1      |   3
4  |     2      |   1
5  |     2      |   2

是否可以用SQL来实现？

Is it possible to achieve this with SQL?

推荐答案

您可以使用 row_number（）：

select p.*, row_number() over (partition by p.company_id order by p.id) as rank
from players p;

要设置值，请使用 update ：

update players p
    set rank = new_rank
from (select p.*, row_number() over (partition by p.company_id order by p.id) as new_rank
      from players p
     ) pp
where pp.id = p.id;

这篇关于设置行的递增和重置值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！