如何使用java从MySQL db中删除重复内容?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (87)

我想使用jdbc删除表中的重复行,只是尝试下面的代码删除表中的所有记录

   String query="Delete from tests where product_id=20 and product_name='KINDLE001'";

有没有办法删除重复记录并保留表中的最后一个重复值

提问于
用户回答回答于

1)定义唯一的键列 - 我在我的例子中使用 KEY1, KEY2

2)定义标识顺序的列 - 保留最高值,所有其他值都被视为重复

create table tab as
select 1 key1, 1 key2, 1 order1 from dual union all  -- dup
select 1 key1, 1 key2, 2 order1 from dual union all  -- dup
select 1 key1, 1 key2, 3 order1 from dual union all
select 1 key1, 2 key2, 1 order1 from dual union all
select 2 key1, 1 key2, 1 order1 from dual union all  -- dup
select 2 key1, 1 key2, 2 order1 from dual union all
select 2 key1, 2 key2, 1 order1 from dual;

此查询标识重复的行

select KEY1, KEY2, ORDER1 from 
 (select tab.*,
   row_number()  over (partition by key1, key2 order by order1 desc) as rn
  from tab)
where rn > 1

      KEY1       KEY2     ORDER1
---------- ---------- ----------
         1          1          2 
         1          1          1 
         2          1          1

并且此查询删除了dulicates

delete from tab where (KEY1, KEY2, ORDER1) in
(select KEY1, KEY2, ORDER1 from 
 (select tab.*,
   row_number()  over (partition by key1, key2 order by order1 desc) as rn
  from tab)
where rn > 1)
用户回答回答于

可以使用下面的方法

Select all unique rows
Copy them to a new temp table
Truncate original table
Copy temp table data to original table

扫码关注云+社区

领取腾讯云代金券