oracle 使用分析函数删除重复行

Delete duplicate rows using Analytical functions

提问by Jeby Sebastian

Can we delete duplicate rows using analytical functions? I mean using row_number()or rankor dense_rank() in Sql query in Oracle?

我们可以使用分析函数删除重复的行吗？我的意思是在 Oracle 的 Sql 查询中使用row_number()orrank或dense_rank()？

回答by JWK

You can use ROW_NUMBER()over a partition of columns that should be unique for you, e.g: ROW_NUMBER() OVER (PARTITION BY COLUMN1, COLUMN2 ORDER BY COLUMN1). Every result that has a rownumber > 1 is a duplicate.

您可以使用ROW_NUMBER()对您来说应该是唯一的列分区，例如：ROW_NUMBER() OVER (PARTITION BY COLUMN1, COLUMN2 ORDER BY COLUMN1). 每个 rownumber > 1 的结果都是重复的。

You can then for example return the rowid's for those and delete them.

例如，您可以返回这些行的 ID 并删除它们。

回答by Aspirant

We can use Analytical Functionsor any other way like Aggregate Functions, 'DISTINCT` to find out whether we have duplicates in our table or not

我们可以使用Analytical Functions或任何其他方式，如Aggregate Functions'DISTINCT` 来确定我们的表中是否有重复项

like using Analytical Functions

喜欢用 Analytical Functions

select col1,col2,col3 from (select tbl.*,rowid rid,row_number() over (partition by col1,col2,col3 order by col1) rnum
from tbl) where rnum>1;

but to delete them we need to get the ROWIDof those whose rnum>1

但要删除它们，我们需要得到ROWID那些rnum>1

delete from tbl where rowid in 
(select rid from (select tbl.*,rowid rid,row_number() over (partition by col1,col2,col3 order by col1) rnum
  from tbl) where rnum>1)

or else we can simply use

否则我们可以简单地使用

 create new_table as select distinct * from old_table

回答by Baskar A

follow these steps to delete duplicate records without using rowid,rownum 1. create table to store distinct values of original table.

按照以下步骤删除重复记录，而不使用 rowid,rownum 1. 创建表来存储原始表的不同值。

    create table newtable as select distinct * from tablename;

1. delete the records from original table.

    truncate table tablename;
   

2. insert a temporary table values into original table.

    insert into tablename select * from newtable; 
   

1. 从原始表中删除记录。

    truncate table tablename;
   

2. 将临时表值插入到原始表中。

    insert into tablename select * from newtable; 
   

回答by Egor Skriptunoff

Yes. But it could be made a bit simpler with aggregate function

是的。但是使用聚合函数可以使它更简单一些

-- delete duplicates
delete from table t1
where t1.rowid not in (
   select min(t2.rowid)
   from table t2
   group by t2.col1, t2.col2, ...
)

fiddle

小提琴

回答by Yamini Sirisha

This is the sort of thing you should do:

这是你应该做的事情：

DELETE  FROM emp
WHERE   rowid IN (
        SELECT  rid
        FROM    (
                  SELECT    rowid rid ,
                            DENSE_RANK() OVER ( PARTITION BY empno ORDER BY rowid ) rn
                  FROM      emp
                ) AS RR
        WHERE   rn > 1 );