1、查找表中重复数据,重复数据以单个字段(title)为标识
select id,title from t_article
where title in ( select title from ( select title as title from t_article
group by title having count(title) > 1
) as title
)order by title
2、删除表中多余的重复数据,重复数据以单个字段(title)为标识,只留有id最小的数据
delete from t_article
where title in ( select title from ( select title as title from t_article
group by title having count(title) > 1
) as title
)and id not in ( select id from ( select min(id) as id from t_article t group by title having count(title) > 1
) as id
);
3、查找表中重复数据,重复数据以(title,source)多个字段为标识
select id,title,source,upload_time from t_article
where (title,source) in ( select title,source from t_article
group by title,source having count(*) > 1)order by title
4、删除表中多余的重复数据,重复数据以(title,source)多个字段为标识,只留有id最小的数据
delete from t_article
where id in ( select id from ( select id from t_article
where (title,source) in ( select title,source from t_article
group by title,source having count(*) > 1
) and id not in ( select id from ( select min(id) as id from t_article
group by title,source having count(*) > 1
) as id
)
)as id
)