删除ORACLE中的半重复行SQL

Question

我的表中有行需要删除，因为有几列是重复的。

e、 g Col1,Col2,Col3,Col4

如果Col1、Col2和Col3是重复的，不管Col4中的值是多少，我希望删除这两个重复的。我该怎么做？

Answer 1

如果存在具有相同col1、col2和col3的另一行具有较低的col4值，则使用EXISTS删除行。即保留一个col1、col2、col3行。

delete from tablename t1
where exists (select 1 from tablename t2
              where t2.col1 = t1.col1
                and t2.col2 = t1.col2
                and t2.col3 = t1.col3
                and t2.col4 < t1.col4)

要删除这两行/所有行，跳过col4条件，改为执行group by：

delete from tablename t1
where exists (select 1 from tablename t2
              where t2.col1 = t1.col1
                and t2.col2 = t1.col2
                and t2.col3 = t1.col3
              group by t2.col1, t2.col2, t2.col3
              having count(*) > 1)

Answer 2

按这些ID分组，并与HAVING检查是否有重复。找到重复后删除记录。

delete from mytable 
where  (col1,col2,col3) in
(
  select col1,col2,col3
  from mytable 
  group by col1,col2,col3
  having count(*) > 1
);

Answer 3

您可以使用place子句来完成此操作：

delete from t
    where (col1, col2, col3) in (select col1, col2, col3
                                 from t
                                 group by col1, col2, col3
                                 having count(*) > 1
                                );