sql，仅保留最大值，并删除其他值 [英] sql, keep only max value and delete others

问题描述

我有这种类型的表：

-id |名称|备忘录

-1 |哥谭| s1ep1

-1 | Gotham | s1ep1

-2 |哥谭| s1ep3

-2 | Gotham | s1ep3

-3 |哥谭| s1ep5

-3 | Gotham | s1ep5

我想保留该条目的max（memo）并删除其他条目，所以只保留第三个（ep5）。

I would like to keep the entry with the max(memo) and delete others, so just keep the third one (ep5).

我可以按如下名称检索所有max（memo）组的结果：

I can retrieve the result of all max(memo) group by name like this :

    SELECT id,max(memo) FROM `reminder` group by name

但是我找不到删除其他人的正确方法，甚至没有看到类似的主题。

But I don't find the proper way to delete others, even looking at similar topics.

我希望像 删除所有不在我的选择范围内。

I expected something like "delete every entries that are not in my selection".

    delete from reminder where not exists (SELECT id,max(memo) FROM `reminder` group by name)

但这是行不通的， 您不能在FROM子句中指定目标表提醒 。我必须做不好。谢谢您的帮助。

But it doesn't work, "You can't specify target table 'reminder' for update in FROM clause". I must do it badly. Thanks for help.

推荐答案

您可以通过 join 来做到这一点：

You can do this with a join:

delete r
    from reminder r left join
         (select name, max(memo) as maxmemo
          from reminder
          group by name
         ) rn
         on r.name = rn.name and r.memo = rn.maxmemo
    where rn.name is null;

放在一边。更常见的是，人们希望保留具有最高 id 的行。结构相同，只是列不同：

As an aside. More typically, one wants to keep the row with the highest id. The structure is the same, just the columns are different:

delete r
    from reminder r left join
         (select name, max(id) as maxid
          from reminder
          group by name
         ) rn
         on r.name = rn.name and r.id = rn.maxid
    where rn.name is null;

这篇关于sql，仅保留最大值，并删除其他值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！