我有这个表CUSTOMER_TEST
与id_1, id_2
可以复制。但是这个表必须只有一对id_1
和id_2
。添加新数据后,这个表看起来像这样:
update_date id_1 id_2 score
13/09/2021 ABC123 ABC456 0.6
13/09/2022 DEF123 DEF456 0.4
13/09/2021 ABC123 ABC789 1.2
14/09/2021 ABC123 ABC456 1
14/09/2022 TEST123 TEST456 0.6
我想通过保持最新的update_date
与更新的数据来更新CUSTOMER_TEST
。期望的结果应该是这样的:
update_date id_1 id_2 score
13/09/2022 DEF123 DEF456 0.4
13/09/2021 ABC123 ABC789 1.2
14/09/2021 ABC123 ABC456 1
14/09/2022 TEST123 TEST456 0.6
我已经尝试过这些代码,但不知道如何更新当前表的结果。因为正如我在Oracle文档上读到的,UPDATE
函数需要指定SET
列
select max(update_date), id_1, id_2, score
from CUSTOMER_TEST
group by id_1, id_2
看来你需要删除而不是更新。使用窗口函数选择要删除的行,然后删除它们。在oracle中,很容易使用内置的元列rowid
。
delete from customer_test x
where x.rowid in (
select i.rid from (
select t.rowid as rid
, row_number() over (partition by t.id_1, t.id_2 order by t.update_date desc) as rn
from customer_test t
) i
where i.rn > 1
)
这并不是说你要更新删除,删除)所有"老"的行比最新的每一个(id_1, id_2)
组合。
:
SQL> select * from customer_test order by update_date, id_1, id_2;
UPDATE_D ID_1 ID_2 SCORE
-------- ------- ------- ----------
13.09.21 ABC123 ABC456 ,6
13.09.21 ABC123 ABC789 1,2
14.09.21 ABC123 ABC456 1
13.09.22 DEF123 DEF456 ,4
14.09.22 TEST123 TEST456 ,6
删除不需要的内容:
SQL> delete from customer_test a
2 where (id_1, id_2, update_date) in (select id_1, id_2, update_date
3 from (select id_1, id_2, update_date,
4 row_number() over (partition by id_1, id_2
5 order by update_date desc) rn
6 from customer_test
7 )
8 where rn > 1
9 );
1 row deleted.
:后
SQL> select * from customer_test order by update_date, id_1, id_2;
UPDATE_D ID_1 ID_2 SCORE
-------- ------- ------- ----------
13.09.21 ABC123 ABC789 1,2
14.09.21 ABC123 ABC456 1
13.09.22 DEF123 DEF456 ,4
14.09.22 TEST123 TEST456 ,6
SQL>