删除所有非重复行，但保留一行 - delete all non-duplicate rows but keep one

delete all non-duplicate rows but keep one

本文关键字：保留一行删除 | 更新日期: 2023-09-27

在MySQL中，我有一个这样的表

book | user | phone
445    a       69
445    g       75
445    e       22
445    l       55
332    w       57
332    u       34
332    v       87
881    o       49

我想删除所有包含book 445的行，并在最后book 332并随机保留其中一行。

所以桌子会变成

book | user | phone
445    l       55
332    w       57
881    o       49

如何在 SQL 中执行此操作？我使用 phyMyAdmin 和 PHP 5.6

谢谢

你来了：

DELETE FROM table1 t1, table1 t2 WHERE t1.user > t2.user AND t1.book = t2.book

将

table1 替换为实际的表名

在MySQL中，您将使用join . 假设user对于赠书是唯一的：

delete t
    from t join
         (select book, max(user) as maxuser
          from t
          group by book
         ) tt
         where t.book = tt.book and t.user < tt.maxuser;

假设book, user是唯一的：

DELETE t1 FROM `table` t1, `table` t2 WHERE t1.user > t2.user AND t1.book = t2.book

如果要

保留"最高"用户，请将>更改为<。

对 sagi 注释的简单更改可以帮助您：

DELETE FROM `YourTable`
        WHERE (`book`,`user`) NOT IN
                (SELECT * FROM (SELECT `book`,MAX(`user`) as id from `YourTable` GROUP BY `book`) as temp)