从 SQL 中的数据库表中删除除 top n 之外的所有内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46385/
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 but top n from database table in SQL
提问by Riri
What's the best way to delete all rows from a table in sql but to keep n number of rows on the top?
从 sql 中的表中删除所有行但在顶部保留 n 行的最佳方法是什么？
采纳答案by Cory Foy
DELETE FROM Table WHERE ID NOT IN (SELECT TOP 10 ID FROM Table)
Edit:
编辑：
Chris brings up a good performance hit since the TOP 10 query would be run for each row. If this is a one time thing, then it may not be as big of a deal, but if it is a common thing, then I did look closer at it.
由于 TOP 10 查询将针对每一行运行，因此 Chris 带来了良好的性能影响。如果这是一次性的事情，那么它可能没有那么大，但如果这是一件常见的事情，那么我确实仔细研究过它。
回答by Chris Miller
I would select ID column(s) the set of rows that you want to keep into a temp table or table variable. Then delete all the rows that do not exist in the temp table. The syntax mentioned by another user:
我会选择 ID column(s) 您想要保留到临时表或表变量中的行集。然后删除临时表中不存在的所有行。另一个用户提到的语法：
DELETE FROM Table WHERE ID NOT IN (SELECT TOP 10 ID FROM Table)
Has a potential problem. The "SELECT TOP 10" query will be executed for each row in the table, which could be a huge performance hit. You want to avoid making the same query over and over again.
有潜在的问题。将为表中的每一行执行“SELECT TOP 10”查询，这可能会对性能造成巨大影响。您希望避免一遍又一遍地进行相同的查询。
This syntax should work, based what you listed as your original SQL statement:
根据您作为原始 SQL 语句列出的内容，此语法应该有效：
create table #nuke(NukeID int)
insert into #nuke(Nuke) select top 1000 id from article
delete article where not exists (select 1 from nuke where Nukeid = id)
drop table #nuke
回答by Simurr
Future reference for those of use who don't use MS SQL.
不使用 MS SQL 的用户的未来参考。
In PostgreSQL use ORDER BYand LIMITinstead of TOP.
在 PostgreSQL 中使用ORDER BYandLIMIT而不是TOP.
DELETE FROM table
WHERE id NOT IN (SELECT id FROM table ORDER BY id LIMIT n);
MySQL -- well...
MySQL——嗯……
Error-- This version of MySQL does not yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
错误-- 此版本的 MySQL 尚不支持“LIMIT & IN/ALL/ANY/SOME 子查询”
Not yet I guess.
我猜还没有。
回答by Tim Wilson
I think using a virtual table would be much better than an IN-clause or temp table.
我认为使用虚拟表会比 IN 子句或临时表好得多。
DELETE
Product
FROM
Product
LEFT OUTER JOIN
(
SELECT TOP 10
Product.id
FROM
Product
) TopProducts ON Product.id = TopProducts.id
WHERE
TopProducts.id IS NULL
回答by Harvey
Here is how I did it. This method is faster and simpler:
这是我如何做到的。这种方法更快更简单：
Delete all but top n from database table in MS SQL using OFFSET command
使用 OFFSET 命令从 MS SQL 中的数据库表中删除除 top n 之外的所有内容
WITH CTE AS
(
SELECT ID
FROM dbo.TableName
ORDER BY ID DESC
OFFSET 11 ROWS
)
DELETE CTE;
Replace IDwith column by which you want to sort.
Replace number after OFFSETwith number of rows which you want to delete.
Choose DESCor ASC- whatever suits your case.
替换ID为要排序的列。用OFFSET要删除的行数替换后面的数字。选择DESC或ASC- 任何适合您的情况。
回答by Noah
This really is going to be language specific, but I would likely use something like the following for SQL server.
这确实是特定于语言的，但我可能会为 SQL 服务器使用类似以下内容。
declare @n int
SET @n = SELECT Count(*) FROM dTABLE;
DELETE TOP (@n - 10 ) FROM dTable
if you don't care about the exact number of rows, there is always
如果你不关心确切的行数，总有
DELETE TOP 90 PERCENT FROM dTABLE;
回答by Mark Biek
I don't know about other flavors but MySQL DELETEallows LIMIT.
我不知道其他风格，但 MySQL DELETE允许 LIMIT。
If you could order things so that the n rows you want to keep are at the bottom, then you could do a DELETE FROM table LIMIT tablecount-n.
如果您可以对事物进行排序，以便您想要保留的 n 行位于底部，那么您可以执行 DELETE FROM table LIMIT tablecount-n。
Edit
编辑
Oooo. I think I like Cory Foy'sanswer better, assuming it works in your case. My way feels a little clunky by comparison.
哎哟。我想我更喜欢Cory Foy 的回答，假设它适用于你的情况。相比之下，我的方式感觉有点笨拙。
回答by Riri
I would solve it using the technique below. The example expect an articletable with an idon each row.
我会使用下面的技术来解决它。该示例需要一个每行都有一个id的文章表。
Delete article where id not in (select top 1000 id from article)
Edit: Too slow to answer my own question ...
编辑：回答我自己的问题太慢了......
回答by Shawn
Refactored?
重构？
Delete a From Table a Inner Join (
Select Top (Select Count(tableID) From Table) - 10)
From Table Order By tableID Desc
) b On b.tableID = A.tableID
edit: tried them both in the query analyzer, current answer is fasted (damn order by...)
编辑：在查询分析器中尝试了它们，当前答案是禁食的（该死的顺序...）
回答by SQLMenace
Better way would be to insert the rows you DO want into another table, drop the original table and then rename the new table so it has the same name as the old table
更好的方法是将您想要的行插入另一个表中，删除原始表，然后重命名新表，使其与旧表具有相同的名称
