sql - SQL：删除两行并在表中插入新行

Question

我想将行对（name="aaa" 的行首先出现，name="bbb" 的行稍后出现，两行具有相同的 id）替换为具有相同 id 但 name="ccc" 的新行。

原表：

timestamp(primary key)        id     name 
2020-02-20...                 1      aaa
2020-02-20...                 1      aaa  -----> first row
2020-02-20...                 1      ddd
2020-02-20...                 1      bbb  -----> second row
2020-02-20...                 2      aaa
2020-02-20...                 2      ccc
2020-02-20...                 2      ddd 

新表：

timestamp(primary key)        id     name 
2020-02-20...                 1      aaa
2020-02-20...                 1      aaa
2020-02-20...                 1      ccc  -----> replaced by this row
2020-02-20...                 1      ddd
2020-02-20...                 2      aaa
2020-02-20...                 2      ccc
2020-02-20...                 2      ddd 

时间戳是主键并且它正在增加。

是否有可能在 PostgresSQL 中实现这一点？

标签： sqlpostgresql

Answer 1

如果我理解正确，这应该做你想要的：

(select id, name
 from t
 where name not in ('aaa', 'bbb') or
       name = 'aaa' and not exists (select 1 from t t2 where t2.id = t.id and t2.name = 'bbb')  or
       name = 'bbb' and not exists (select 1 from t t2 where t2.id = t.id and t2.name = 'aaa')           
) union all
(select id, 'ccc'
 from t
 where name in ('aaa', 'bbb')
 group by id
 having count(*) = 2
)