oracle SQL删除表中最旧的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3131112/
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
SQL to delete the oldest records in a table
提问by Liam
I'm looking for a single SQL query to run on an oracle table that will retain n number of records in a table and delete the rest
我正在寻找在 oracle 表上运行的单个 SQL 查询，该表将保留表中的 n 条记录并删除其余记录
I tried the following
我尝试了以下
delete from myTable where pk not in
(SELECT pk FROM myTable where rownum <5 order by created DESC)
But it appears that I cannot have order byin the nested select.
但看起来我不能order by在嵌套选择中。
Any help appreciated
任何帮助表示赞赏
回答by Tony Andrews
When you use ORDER BY with ROWNUM the ROWNUM is applied first, so you don't get the results you expect. You could modify your SQL to:
当您将 ORDER BY 与 ROWNUM 一起使用时，首先应用 ROWNUM，因此您不会获得预期的结果。您可以将 SQL 修改为：
delete from myTable where pk not in
( SELECT pk FROM
( SELECT pk FROM myTable order by created DESC)
where rownum <5
)
There are many other ways to write this. If the table is large and most rows will be deleted then maybe this will be faster:
有很多其他的方法来写这个。如果表很大并且大多数行将被删除，那么这可能会更快：
delete from myTable where created <
( SELECT MIN(created) FROM
( SELECT created FROM myTable order by created DESC)
where rownum <5
)
