mysql - 连接多个 MySQL 查询以及分组和删除记录

Question

我有一个具有以下结构的表:

   ID Class Name   Email            Timestamp
    1 A     Ramesh ramesh@test.com  2014/03/07 17:29:34
    2 A     Ramesh ramesh@test.com  2014/03/12 17:29:34
    3 B     Kartik ramesh@test.com  2014/03/12 12:29:34
    4 B     Kartik ramesh@test.com  2014/03/17 12:29:34
    5 A     Ramesh newmail@test.com 2014/03/12 17:29:34
    6 C     Vinay  ramesh@test.com  2014/03/12 17:29:34

我想选择具有相同名称、类别和电子邮件且具有多个记录的记录，并仅保留最新记录并删除所有其他记录。

例如上表的输出需要是:

   ID Class Name   Email            Timestamp
    2 A     Ramesh ramesh@test.com  2014/03/12 17:29:34
    4 B     Kartik ramesh@test.com  2014/03/17 12:29:34
    5 A     Ramesh newmail@test.com 2014/03/12 17:29:34
    6 C     Vinay  ramesh@test.com  2014/03/12 17:29:34

我尝试了分组语句，但它不起作用。

Answer 1

DELETE x 
  FROM my_table x 
  LEFT
  JOIN 
     ( SELECT class,name,MAX(timestamp) max_timestamp FROM my_table GROUP BY class,name) y 
    ON y.class = x.class 
   AND y.name = x.name 
   AND y.max_timestamp = x.timestamp
 WHERE y.class IS NULL;