首先是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/

10-10 18:42
查看更多