sql - 带有大小写和子查询的 PostgreSQL UPDATE 以从正上方的行中获取值

Question

考虑下表:

+-------+-----+----+
| genre | ref | id |
+-------+-----+----+
| CIT   | 000 |  1 |
| RP    | 111 |  2 |
| RV    |     |  3 |
| RP    | 777 |  4 |
| HY    |     |  5 |
| RB    | 222 |  6 |
| RV    |     |  7 |
+-------+-----+----+ 

我正在寻找一个 UPDATE 语句，该语句将检查类型为“RP”或“RB”的行之后的行是否是类型为“RV”的行，如果是，则它应该更新为来自它正上方的行(总是在正上方；'id-1')。所以在这个测试用例中，它看起来像这样:

+-------+-----+----+
| genre | ref | id |
+-------+-----+----+
| CIT   | 000 |  1 |
| RP    | 111 |  2 |
| RV    | 111 |  3 |
| RP    | 777 |  4 |
| HY    |     |  5 |
| RB    | 222 |  6 |
| RV    | 222 |  7 |
+-------+-----+----+ 

非常感谢您的任何建议!

Answer 1

我的第一个想法是使用窗口函数:

update t
    ref = tt.ref
from (select t.*,
             lag(ref) over (order by id) as prev_ref
      from t
     ) tt
where tt.id = t.id and
      t.genre = 'RV' and
      t.prev_genre in ('RP', 'RB');

如果你碰巧知道id之间没有空隙，那么你可以使用join:

update t
    set t.ref = tprev.ref
from t tprev cross join
     t tnext
where tprev.id = t.id - 1 
      t.genre = 'RV' and
      tprev.genre in ('RP', 'RB');