sql - 如何规范化/更新 "order"列

Question

我有一个包含一些数据数据的表“mydata”:

id    name       position
===========================
4     foo        -3 
6     bar        -2 
1     baz        -1
3     knork      -1
5     lift       0
2     pitcher    0

我获取使用 按位置 ASC 排序的表;

position 列值可能不是唯一的(由于某些原因未在此处描述 :-)，并且用于在 SELECT 期间提供自定义订单。

我想做什么:

我想通过将唯一位置关联到不破坏顺序的每一行来规范化表列“位置”。此外归一化后的最高位置应该是-1。

希望得到的表格内容:

id    name       position
===========================
4     foo        -6 
6     bar        -5 
1     baz        -4
3     knork      -3
5     lift       -2
2     pitcher    -1

我尝试了几种方法，但未能实现正确 update 语句。

我猜是用

generate_series( -(select count(*) from mydata), -1) 

是获取位置列新值的一个很好的起点，但我不知道如何将生成的列数据合并到更新语句中。

希望有人能帮助我:-)

Answer 1

类似于:

with renumber as (
  select id,
         -1 * row_number() over (order by position desc, id) as rn
  from foo
)
update foo
  set position = r.rn
from renumber r
where foo.id = r.id
  and position <> r.rn;

SQLFiddle Demo