mysql - MYSQL中基于group by删除记录

Question

我无法提出一个查询，该查询将允许我仅保留用户的最新订单(也许更好的说法是删除所有旧订单):

CREATE TABLE orders(id integer, created_at datetime, user_id integer, label nvarchar(25));

INSERT INTO orders values(1, now(), 1, 'FRED FIRST');

INSERT INTO orders values(2, DATE_ADD(now(), INTERVAL 1 DAY), 1, 'FRED SECOND');

INSERT INTO orders values(3, DATE_ADD(now(), INTERVAL 2 DAY), 1, 'FRED THIRD');

INSERT INTO orders values(4, DATE_ADD(now(), INTERVAL 1 DAY), 3, 'BARNEY FIRST');

SELECT * FROM orders;

'1','2014-03-07 08:39:36','1','FRED FIRST'
'2','2014-03-08 08:39:36','1','FRED SECOND'
'3','2014-03-09 08:39:36','1','FRED THIRD'
'4','2014-03-08 08:39:36','3','BARNEY FIRST'

我想运行一个查询，该查询将留下 FRED 的第三个订单和 BARNEY 的第一个订单。 FRED FIRST 和 FRED SECOND 应删除，因为它们不是 FRED 的最新订单。

对于如何通过单个查询来完成此操作有什么想法吗？

编辑:发布此内容后，我发现了一些有用的东西(它完成了我想要做的事情)——但看起来有点困惑:

DELETE  old_orders
FROM orders old_orders
left outer join(
SELECT MAX(created_at) as created_at, user_id
FROM orders
GROUP BY user_id) new_orders
ON new_orders.user_id = old_orders.user_id and new_orders.created_at = old_orders.created_at
WHERE new_orders.user_id is null;

Answer 1

使用嵌套查询，如下所示:

DELETE FROM orders
WHERE id NOT IN (
  SELECT id FROM (
    select id from orders o JOIN (
      select user_id, max(created_at) t from orders group by user_id
    ) o1 ON o.user_id = o1.user_id AND o.created_at = o1.t
  ) AS tmp
)

工作 fiddle :http://sqlfiddle.com/#!2/56d913/1