【数据库】Mysql删除重复记录只保留一条

实际工作中，有可能会对数据库中的数据进行再次加工。假设有个表记录的是技术文章，有三个字段：st_link（文章链接）、st_title（文章章节标题）、st_name（文章名称）

假设初始时主键为st_link，会出现1个st_title对应多个st_link的情况。但是后面发现其实一个st_title不同st_link里面的内容都一样，现在需要将每个st_title只保留一个st_link，并且将st_title作为主键。

下面说一下具体实现步骤：

1. 先查找出st_title有多个st_link的情况

select st_link from SIS_story where st_title in(
			select st_title from SIS_story group by st_title having count(*)>1);

2.查出每个st_title需要保留一条st_link的记录,这里保留排序小的那一条

select min(st_link) from SIS_story group by st_title having count(*)>;

3.上面两步合并，查出需要删除的记录

select st_link from SIS_story where st_title in(
			select st_title from SIS_story group by st_title having count(*)>1)
			and st_link not in (select min(st_link) from SIS_story group by st_title having count(*)>1)

4.上面查出来的是 针对st_title有多个st_link的情况，过滤需要保留的记录。这时是不是可以直接删除数据了呢？这时候是不能直接删除的，需要将查询信息放到一个临时表：

select st_link from (
		select st_link from SIS_story where st_title in(
			select st_title from SIS_story group by st_title having count(*)>1)
			and st_link not in (select min(st_link) from SIS_story group by st_title having count(*)>1)
	) as t

5.最后再进行删除操作

delete from SIS_story where st_link in (
	select st_link from (
		select st_link from SIS_story where st_title in(
			select st_title from SIS_story group by st_title having count(*)>1)
			and st_link not in (select min(st_link) from SIS_story group by st_title having count(*)>1)
	) as t
);