SQL 从 Oracle 表中删除除最旧的所有重复记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5104485/
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
Delete all duplicate records from Oracle table except oldest
提问by Ciaran Bruen
I have 2 tables, one parent TableA and one child TableB. TableB has 1 or more records with a parent record in TableA. I need to delete all records from TableB except the earliest date i.e. all duplicates in TableB. I don't think TableA needs to be involved in the statement but I'm including it just for reference.
我有 2 个表，一个父 TableA 和一个子 TableB。表B 有1 条或多条记录，在表A 中有一个父记录。我需要从 TableB 中删除除最早日期之外的所有记录，即 TableB 中的所有重复项。我不认为 TableA 需要参与该声明，但我将其包含在内仅供参考。
TableA
_______
SecID, SecName
1, Sec1
2, Sec2
3, Sec3
4, Sec4
TableB
_________
IncID, SecID, PayDate
16, 1, 11/03/2011
17, 1, 11/04/2011
18, 2, 10/01/2011
19, 3, 01/06/2011
20, 3, 01/09/2011
21, 3, 01/12/2011
22, 4, 10/06/2011
So in TableB above I need to delete records 17, 20, and 21 leaving one record for each SecID. So far I have below but for some reason it's including the earliest record which I want to keep:
因此，在上面的 TableB 中，我需要删除记录 17、20 和 21，为每个 SecID 留下一条记录。到目前为止，我有以下但出于某种原因，它包括我想保留的最早记录：
delete from TableB where PayDate not in (
select min(PayDate)from TableB
having ( count(PayDate) > 1 )
)
回答by Vincent Malgrat
you can use ROWID and analytics:
您可以使用 ROWID 和分析：
SQL> DELETE FROM tableB
2 WHERE ROWID NOT IN
3 (SELECT first_value(ROWID)over(PARTITION BY secID ORDER BY paydate)
4 FROM tableB);
3 rows deleted
SQL> select * from tableB;
INCID SECID PAYDATE
---------- ---------- -----------
16 1 11/03/2011
18 2 10/01/2011
19 3 01/06/2011
22 4 10/06/2011
You could also use a more conventional semi-join:
您还可以使用更传统的半连接：
SQL> DELETE FROM tableB b_out
2 WHERE EXISTS (SELECT NULL
3 FROM tableB b_in
4 WHERE b_in.secID = b_out.secID
5 AND b_in.paydate < b_out.paydate);
3 rows deleted
回答by Bala Kumar
TABLE
桌子
ID RefCode Code_Desc
122 B122 The Notebook
122 B122 The Notebook
122 B122 The Notebook
123 B123 A Walk to Remember
123 B123 A Walk to Remember
123 B123 A Walk to Remember
123 B123 A Walk to Remember
To delete All duplicate records except one
删除除一条之外的所有重复记录
delete from TABLE a where rowid<(select max(rowid) from TABLE b where a.ID = b.ID)
delete from TABLE a where rowid<(select max(rowid) from TABLE b where a.ID = b.ID)
To delete Specific duplicate records except one
删除除一条之外的特定重复记录
delete from TABLE a where rowid<(select max(rowid) from TABLE b where a.ID = b.ID and a.ID = 122)
delete from TABLE a where rowid<(select max(rowid) from TABLE b where a.ID = b.ID and a.ID = 122)
回答by Süniúr
delete from your_table a
where a.rowid not in
(
select max(b.rowid) from your_table b
group by b.col1,b.col2....b.coln
)
This will get alluniquerowids and except these rowid-s the sql will delete all rows.
这将获得所有唯一的rowids，除了这些 rowid-s，sql 将删除所有行。
