MySQL 根据最大值删除一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3620940/
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
Deleting a row based on the max value
提问by Robert de Klerk
How can I structure a mySQL query to delete a row based on the max value.
如何构建一个 mySQL 查询以根据最大值删除一行。
I tried
我试过
WHERE jobPositonId = max(jobPostionId)
but got an error?
但有错误？
回答by Konerak
DELETE FROM table ORDER BY jobPositonId DESC LIMIT 1
回答by OMG Ponies
Use:
用：
DELETE FROM TABLE t1
JOIN (SELECT MAX(jobPositonId) AS max_id FROM TABLE) t2
WHERE t1.jobPositonId = t2.max_id
Mind that allthe rows with that jobPositonIdvalue will be removed, if there are duplicates.
请注意，如果有重复项，将删除具有该值的所有行jobPositonId。
The stupid part about the 1093 error is that you can get around it by placing a subquery between the self reference:
关于 1093 错误的愚蠢部分是您可以通过在自引用之间放置子查询来解决它：
DELETE FROM TABLE
WHERE jobPositonId = (SELECT x.id
FROM (SELECT MAX(t.jobPostionId) AS id
FROM TABLE t) x)
Explanation
解释
MySQL is only checking, when using UPDATE& DELETEstatements, if the there's a first level subquery to the same table that is being updated. That's why putting it in a second level (or deeper) subquery alternative works. But it's only checking subqueries - the JOIN syntax is logically equivalent, but doesn't trigger the error.
MySQL 仅在使用UPDATE&DELETE语句时检查是否存在针对正在更新的同一表的第一级子查询。这就是为什么将它放在第二级（或更深）子查询替代方案中的原因。但它只是检查子查询 - JOIN 语法在逻辑上是等效的，但不会触发错误。
回答by el_quick
DELETE FROM `table_name` WHERE jobPositonId = (select max(jobPostionId) from `table_name` limit 1)
OR
或者
DELETE FROM `table_name` WHERE jobPositonId IN (select max(jobPostionId) from `table_name` limit 1)
回答by sdfor
This works:
这有效：
SELECT @lastid := max(jobPositonId ) from t1;
DELETE from t1 WHERE jobPositonId = @lastid ;
Other than going to the database twice, is there anything wrong with this technique?
除了两次访问数据库之外，这种技术有什么问题吗？
