查找有多个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)
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)
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)