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

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

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

1.查询表中数据

select * from test ;

[table]
| 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 |
[/table]
执行操作后

select * from test ;

[table]
| id | name | value |
| 1 | p | p |
| 3 | pp | pp |
| 5 | ppp | ppp |
| 7 | pppp | pppp |
[/table]

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

[table]
| id |
| 1 |
| 3 |
| 5 |
| 7 |
[/table]
3.查询相同数据的比较大的id值

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

[table]
| max(id) |
| 2 |
| 4 |
| 6 |
| 8 |
[/table]