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扫描下方二维码关注公众号和小程序↓↓↓
Loading...