mysql - 如何删除基于 2 列的旧重复行但保留最新行？

Question

所以我有这张表（称为test_table）

id  |  hotel_id  |  user_id
1   |  1         |  1
2   |  1         |  1
3   |  1         |  2
4   |  2         |  3
5   |  1         |  2
6   |  3         |  3

因此，如果 hotel_id 和 user_id 相同，那么我想删除重复的行但保留最新的行（最新的行是较高的行id）。

因此，删除我的表格后将如下表所示。

我删除id 1了，因为有一个较新的行id 2。

我删除id 3了，因为有一个较新的行id 5。

id  |  hotel_id  |  user_id
2   |  1         |  1
4   |  2         |  3
5   |  1         |  2
6   |  3         |  3

我尝试使用下面的代码，但它只检查一列是否重复。最有效的方法是什么？

delete test_table
   from test_table
  inner join (
     select max(id) as lastId, hotel_id
       from test_table
      group by hotel_id
     having count(*) > 1) duplic on duplic.hotel_id = test_table.hotel_id
  where test_table.id < duplic.lastId;

标签： mysqlsqldatabasegroup-bysql-delete

Answer 1

MySQL 中的传统方式使用JOIN：

delete tt
    from test_table tt join
         (select tt.hotel_id, tt.user_id, max(tt.id) as max_id
          from test_table tt
          group by tt.hotel_id, tt.user_id
         ) tokeep
         on tokeep.hotel_id = tt.hotel_id and
            tokeep.user_id = tt.user_id and
            tokeep.max_id > tt.id;

如果id在表中是唯一的，则可以简化为：

delete tt
    from test_table tt left join
         (select tt.hotel_id, tt.user_id, max(tt.id) as max_id
          from test_table tt
          group by tt.hotel_id, tt.user_id
         ) tokeep
         on tt.id = tokeep.max_id
     where to_keep.max_id is null;