mySQL 子查询限制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7124418/
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
mySQL subquery limit
提问by André Al?ada Padez
This is probably an easy one... how can I achieve what i want with this query:
这可能是一个简单的......我怎样才能通过这个查询实现我想要的：
delete from posts where id not in
(SELECT id FROM posts order by timestamp desc limit 0, 15)
so, to put it in a nutshell, I want to delete every post that isn't on the latest 15.
所以，简而言之，我想删除所有不在最新 15 条上的帖子。
When I try that query, I get that
当我尝试该查询时，我明白了
MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery
MySQL 尚不支持 'LIMIT & IN/ALL/ANY/SOME 子查询
EDIT
编辑
mySQL Server version: 5.5.8
mySQL Client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $
Error: #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
错误：#1235 - 此版本的 MySQL 尚不支持“LIMIT & IN/ALL/ANY/SOME 子查询”
回答by Nicola Cossu
Try this:
尝试这个：
DELETE
FROM posts
WHERE id not in (
SELECT * FROM (
SELECT id
FROM posts
ORDER BY timestamp desc limit 0, 15
)
as t);
回答by Ertugrul Yilmaz
You can try this:
你可以试试这个：
DELETE
p1.*
FROM
posts p1 INNER JOIN
(SELECT
id
FROM
posts
ORDER BY timestamp DESC
LIMIT 0, 15
) AS p2
ON p1.id = p2.id;
回答by Samuel Kwame Antwi
Since the newest 15 will always come from the first 15 if you order them by descending order.You can just delete any id that did not make it into the first 15. like so i just tried it and it worked fine. Hopefully it helps someone
因为如果你按降序排列，最新的 15 个总是来自前 15 个。你可以删除任何没有进入前 15 个的 id。就像我刚刚尝试过的那样，它工作正常。希望它可以帮助某人
Delete from `table` where id not in (SELECT * FROM (Select id from `table` order by id desc limit 15) as derivedTable);
