陵水专业网站建设,wordpress 提交表单,世界羽联巡回赛总决赛,南京高端模板建站实际工作中#xff0c;有可能会对数据库中的数据进行再次加工。假设有个表记录的是技术文章#xff0c;有三个字段#xff1a;st_link#xff08;文章链接#xff09;、st_title#xff08;文章章节标题#xff09;、st_name#xff08;文章名称#xff09;
假设初始…实际工作中有可能会对数据库中的数据进行再次加工。假设有个表记录的是技术文章有三个字段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
);
