MySQL的子查询限制
问题描述:
这可能是一个容易的......我怎么能达到什么样的,我想这个查询:MySQL的子查询限制
delete from posts where id not in
(SELECT id FROM posts order by timestamp desc limit 0, 15)
所以,把它概括地说,我想删除每一个岗位ISN 'T最新的15
当我尝试查询,我得到的MySQL还不支持' LIMIT & IN/ALL/ANY/SOME子查询
编辑
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'
答
试试这个:
DELETE
FROM posts
WHERE id not in (
SELECT * FROM (
SELECT id
FROM posts
ORDER BY timestamp desc limit 0, 15
)
as t);
答
你可以试试这个:
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;
答
由于最新的15总是会被降order.You可以删除任何标识来自前15,如果你命令他们这并没有让它进入前15个。像这样我只是试了一下,它工作得很好。希望它可以帮助别人
Delete from `table` where id not in (SELECT * FROM (Select id from `table` order by id desc limit 15) as derivedTable);
什么是MySQL版本? MySQL给出的EXACT错误是什么? – Shef