SQL 删除除某些行以外的所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6989932/
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 delete all rows except some ones
提问by Addev
I have a table with the following columns (of urls):
我有一个包含以下列（网址）的表格：
[id,url,visited,timestamp]
Types:[int,string,int,long]
I want to:
我想要：
Delete all urls except 10 unvisited priorizing higher timestamp (or delete all if all are visited for example)
删除所有 url，除了 10 个未访问的优先级更高的时间戳（例如，如果所有都被访问，则删除所有）
Its posible to do that in a single query? Anyway whats the best query (queries) for doing it?
它可以在单个查询中做到这一点吗？无论如何，最好的查询（查询）是什么？
Thanks in advance
提前致谢
回答by dizzwave
I don't think TOP works in sqlite -- need to use LIMIT
我认为 TOP 在 sqlite 中不起作用——需要使用 LIMIT
DELETE FROM mytable WHERE id NOT IN (
SELECT id FROM mytable
WHERE visited = false
ORDER BY timestamp DESC
LIMIT 10
)
回答by Travis
DELETE FROM tableofDeletion
WHERE
-- Delete all items not in the following select
-- ordered by the timestamp so we can get the top 10
id NOT IN (SELECT id
FROM tableofDeletion
WHERE
visited = 0 -- false
ORDER BY timestamp DESC
LIMIT 10)
I think this delivers what you're looking for.
我认为这提供了你正在寻找的东西。
