首先是SQL =
delete
from eat_history
where food in
( select food
from eat_history
where name ='minicake'
and cream='yes'
order
by eat_time desc
limit 1
) t1
我的意图是删除名称为“ minicake”和cream =“ yes”(可能有多个项目)的最新食用食品。正如我在标题中指出的那样,MySQL不允许这样做。但是,就我而言,“极限1”是关键点。有没有好的解决方法,最好是单个SQL语句?
=====
编辑:我在另一个问题中找到了一个答案,尽管MySQL不允许在IN子句中限制,但在子子查询中是允许的,例如最初在“选择食物...”之外添加“选择*来自”可以解决该限制。说真的,我不知道为什么,但是我测试了,没关系。
最佳答案
首先,您应该在in子句之后删除t1
delete from eat_history where food in (
select food from eat_history where name='minicake' and cream='yes'
order by eat_time DESC limit 1
)
但您可以通过联接使用组来避免限制
delete from eat_history e
inner join (
select food, max(eat_time) max_time
from eat_history
where name='minicake' and cream='yes'
group by foo
) t on t.food = e.food and t.max_time = e.eat_time
关于mysql - MySQL中不允许子查询中的限制,但我需要“它”,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/50393848/