如何从 Oracle 数据库中删除重复的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10537413/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete duplicate rows from an Oracle Database?
提问by GIS-Jonathan
We have a table that has had the same data inserted into it twice by accident meaning most (but not all) rows appears twice in the table. Simply put, I'd like an SQL statement to delete one version of a row while keeping the other; I don't mind which version is deleted as they're identical.
我们有一个表，它意外地将相同的数据插入了两次，这意味着大多数（但不是全部）行在表中出现了两次。简单地说，我想要一条 SQL 语句来删除一行的一个版本，同时保留另一个版本；我不介意删除哪个版本，因为它们是相同的。
Table structure is something like:
表结构类似于：
FID, unique_ID, COL3, COL4....
FID、unique_ID、COL3、COL4....
Unique_IDis the primary key, meaning each one appears only once.
FIDis a key that is unique to each feature, so if it appears more than once then the duplicates should be deleted.
Unique_ID是主键，意思是每一个只出现一次。
FID是每个功能唯一的键，因此如果它出现多次，则应删除重复项。
To select features that have duplicates would be:
选择具有重复的特征将是：
select count(*) from TABLE GROUP by FID
select count(*) from TABLE GROUP by FID
Unfortunately I can't figure out how to go from that to a SQL delete statement that will delete extraneous rows leaving only one of each.
不幸的是，我不知道如何从那个转到 SQL 删除语句，该语句将删除无关的行，每行只留下一个。
This sort of question has been asked before, and I've tried the create table with distinct, but how do I get all columns without naming them? This only gets the single column FID and itemising all the columns to keep gives an: ORA-00936: missing expression
以前有人问过这种问题，我已经尝试过使用不同的创建表，但是如何在不命名的情况下获取所有列？这仅获取单列 FID 并逐项列出要保留的所有列给出：ORA-00936: missing expression
CREATE TABLE secondtable NOLOGGING as select distinct FID from TABLE
CREATE TABLE secondtable NOLOGGING as select distinct FID from TABLE
回答by Justin Cave
If you don't care which row is retained
如果您不在乎保留哪一行
DELETE FROM your_table_name a
WHERE EXISTS( SELECT 1
FROM your_table_name b
WHERE a.fid = b.fid
AND a.unique_id < b.unique_id )
Once that's done, you'll want to add a constraint to the table that ensures that FIDis unique.
完成后，您需要向表中添加一个约束以确保它FID是唯一的。
回答by bitoshi.n
Try this
尝试这个
DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.FID = B.FID)
回答by hogni89
A suggestion
一条建议
DELETE FROM x WHERE ROWID IN
(WITH y AS (SELECT xCOL, MIN(ROWID) FROM x GROUP BY xCOL HAVING COUNT(xCOL) > 1)
SELCT a.ROWID FROM x, y WHERE x.XCOL=y.XCOL and x.ROWIDy.ROWID)
回答by Shahriar Hasan Sayeed
Try with this.
试试这个。
DELETE FROM firsttable WHERE unique_ID NOT IN
(SELECT MAX(unique_ID) FROM firsttable GROUP BY FID)
EDIT:One explanation:
编辑：一种解释：
SELECT MAX(unique_ID) FROM firsttable GROUP BY FID;
This sql statement will pick each maximum unique_ID row from each duplicate rows group. And delete statement will keep these maximum unique_ID rows and delete other rows of each duplicate group.
此 sql 语句将从每个重复行组中选择每个最大的 unique_ID 行。而 delete 语句将保留这些最大的 unique_ID 行并删除每个重复组的其他行。
回答by GongchuangSu
You can try this.
你可以试试这个。
delete from tablename a
where a.logid, a.pointid, a.routeid) in (select logid, pointid, routeid from tablename
group by logid, pointid, routeid having count(*) > 1)
and rowid not in (select min(rowid) from tablename
group by logid, pointid, routeid having count(*) > 1)
