mysql - 按顺序更新列的 psql 查询或函数

Question

我有一个“学生”表，如下所示:

Name    | Number
--------+-------
john    | 1  
Jessy   | 2  
darine  | 3  
Prince  | 5  
queeny  | 7  

更新后的表格应如下所示:

Name    | Number
--------+-------
john    | 1  
Jessy   | 2  
darine  | 3  
Prince  | 4  
queeny  | 5  

Answer 1

postgres=# SELECT * FROM foo ORDER BY number;
  name  | number 
--------+--------
 john   |      1
 jessy  |      2
 darine |      3
 prince |      5
 queeny |      7

postgres=# SELECT name, number, row_number() OVER (ORDER BY number) FROM foo;
  name  | number | row_number 
--------+--------+------------
 john   |      1 |          1
 jessy  |      2 |          2
 darine |      3 |          3
 prince |      5 |          4
 queeny |      7 |          5

UPDATE foo SET number = nums.row_number
FROM (
    SELECT name, row_number() OVER (ORDER BY number) FROM foo
) nums
WHERE foo.name = nums.name;

postgres=# SELECT * FROM foo ORDER BY number;
  name  | number 
--------+--------
 john   |      1
 jessy  |      2
 darine |      3
 prince |      4
 queeny |      5