mysql删除数据表中的重复记录保留i…

删除tb_album_item_copy表中source_id,type字段重复的记录，并且保留id最小的记录

DELETE FROM tb_album_item_copy WHERE id in

(

SELECT a.id FROM(

SELECT id from tb_album_item_copy where source_id in (

select source_id from tb_album_item_copy group by source_id,type having count(*) > 1) 

AND type in (select type from tb_album_item_copy group by source_id,type having count(*) > 1) 

and id not in (select min(id) from tb_album_item_copy group by source_id,type having count(*)>1) 

)a

)

 

查询source_id,type字段重复的记录

select source_id,type from tb_album_item_copy group by source_id,type having count(*) > 1

 

查询id不是最小的重复记录

SELECT id from tb_album_item_copy where source_id in (

select source_id from tb_album_item_copy group by source_id,type having count(*) > 1)

AND type in (select type from tb_album_item_copy group by source_id,type having count(*) > 1)

and id not in (select min(id) from tb_album_item_copy group by source_id,type having count(*)>1)

 

备注：

1：//由于mysql不支持where（字段1，字段2）in（select 字段，字段2 from 表1）类型的in查询，所以此处只能分开处理。

2：//由于mysql查询出来的结果不能直接作为删除条件，所以要借助中间查询（即临时表，此处即为a）进行删除。

SELECT a.id FROM(查询结果)a

此种在mysql中会出现“You can't specify target table 'tb_album_item_copy' for update in FROM clause”这样的错误提示

删除重复示例只保留一条

 Delete from tb_album_info Where id Not In (select id from (Select id From tb_album_info Group By source_id,album_item_id) as temp )