MySQL-删除除id外其他内容都相同的数据

删除除id外其他内容都相同的数据

难点：自己不能删除自己，要进行多次转换

1.查询表中数据

select * from test ;

id	name	value
1	p	p
2	p	p
3	pp	pp
4	pp	pp
5	ppp	ppp
6	ppp	ppp
7	pppp	pppp
8	pppp	pppp

执行操作后

select * from test ;

id	name	value
1	p	p
3	pp	pp
5	ppp	ppp
7	pppp	pppp

2.操作

delete a
from test a left join(
select (id) from test group by name,value
)b on a.id=b.id
where b.id is null ;

delete from test where id not in
(
 select a.id from

  (
	select id from test a where exists 
 
        (
     	       select id from test  b  group by name,value HAVING count(b.id)> 1 
    
        ) group by name,value 

  )a 

);

说明，若未指定id是查询中最大的id还是最小的id，默认为id最小的

select id from test  b  group by name,value HAVING count(b.id)> 1

id

  1

  3

  5

  7

3.查询相同数据的比较大的id值

select max(id) from test group by name,value having count(id)>1;

max(id)

       2

       4

       6

       8