SQL 删除重复数据保留最新的一条

SQL 面试 About 611 words

数据表

mysql> select * from test;
+----+--------+---------------------+
| id | name   | cdate               |
+----+--------+---------------------+
|  1 | abc    | 2022-02-08 16:45:13 |
|  2 | xxxxx  | 2022-02-08 17:04:32 |
|  3 | xxxxx  | 2022-02-08 16:45:13 |
|  4 | zzzsss | 2022-02-08 16:45:13 |
|  5 | aaa    | 2022-02-08 16:45:13 |
|  6 | zzz    | 2022-02-08 16:45:13 |
+----+--------+---------------------+

MySQL

delete from test where id not in (
    select t.id from (
        select max(id) as id from test group by name
    ) t
)

解析

关键在于分组后找到最新的一条数据,可以在group by后使用聚合函数max(),将最大的id取出来,然后删除条件使用not in

Views: 3,015 · Posted: 2022-04-11

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb/LiteNote

扫描下方二维码关注公众号和小程序↓↓↓

扫描下方二维码关注公众号和小程序↓↓↓


Today On History
Browsing Refresh