mysql数据库中怎么删除重复数据?

xfyanmeng 2018-02-25 07:06:36
程序是php的,name字段有重复的,根据time字段记录的时间保留最新的。
有没有什么好的方法?
...全文
1403 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
qq_20899941 2018-04-18
  • 打赏
  • 举报
回复
delete from table where id not in (select id from (select id from (select id,name from table order by time desc)c group by name)b)
qq_20899941 2018-04-18
  • 打赏
  • 举报
回复
delete from table where id not in (select id from (select id from (select id,name from table order by time)c group by name)b)
zxh_33 2018-04-02
  • 打赏
  • 举报
回复
查找有多个time的name和重复次数: 用count+group by计算每个name下time的记录数,having筛出一个name有多个time的: select name,count(time) as count From 表 Group By name Having count>1 或者查询重复记录的所有字段: select * from 表 where name in (select name From 表 Group By name Having count(time)>1) 删除数据重复的行,MySQL用以下语句会Error Code: 1093. You can't specify target table 'table' for update in FROM clause): delete from 表 where name in (select name From 表 Group By name Having count(name)>1) and time not in (select max(time)from 表 group by name Having count(name)>1) 因为MySQL对同一个表不能在修改的同时查询。所以用以下语句,加入临时表即可: delete from test where name in (select * from(select name From test Group By name Having count(name)>1) as _namelist) and time not in (select * from(select max(time)from test group by name Having count(name)>1)as _timelist)
董哥007 2018-04-01
  • 打赏
  • 举报
回复
DELETE from str where add_time not in (SELECT s.mt from (SELECT MIN(add_time) mt from str GROUP BY name) s); str是你的表名。亲测可行。
深圳phper 2018-02-27
  • 打赏
  • 举报
回复
DELETE FROM yourtable WHERE `time` in (SELECT `time` from (SELECT `time` FROM yourtable where `name` in (SELECT `name` FROM yourtable GROUP BY `name` HAVING count(1)>1) AND `time` NOT in (SELECT min(`time`) FROM yourtable GROUP BY `name` HAVING count(1)>1)) a)
不喝水的鱼 2018-02-27
  • 打赏
  • 举报
回复
想清理数据的话,可以使用建设临时表的方式:将time最新的数据查询出来放到临时的新表里面,然后将原表备份一下后清空,再讲临时表的数据弄进去就行! 一定记得备份,备份,备份
  • 打赏
  • 举报
回复
查询出来,比较判断,根据time删除
徐卡丘 2018-02-26
  • 打赏
  • 举报
回复
DELETE FROM test WHERE name = 'tt' AND time NOT IN ( select * from ( SELECT max(time) FROM test GROUP BY name HAVING count(name) > 1 )as a );
  • 打赏
  • 举报
回复
delete from test where id not in (select b.id from(select id,name,`time` from test as t where time=(select max(t1.time) from test as t1 where t.name = t1.name)) as b)

21,882

社区成员

发帖
与我相关
我的任务
社区描述
从PHP安装配置,PHP入门,PHP基础到PHP应用
社区管理员
  • 基础编程社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧