查询并删除多个字段重复记录
时间：2010-10-11 来源：pigpiggrass
查找表中多余的重复记录（多个字段）
例子
表名:suite
查找suite表中其中5个字段重复的记录,这五个字段分别是comp_id,proj_id,buil_id,part_id,id
查询的SQL代码如下: SELECT *
FROM suite a INNER JOIN
(SELECT comp_id, proj_id, buil_id, part_id, id
FROM suite
GROUP BY comp_id, proj_id, buil_id, part_id, id
HAVING COUNT(*) > 1) b ON a.COMP_ID = b.comp_id AND
a.PROJ_ID = b.proj_id AND a.BUIL_ID = b.buil_id AND a.PART_ID = b.part_id AND
a.id = b.id
ORDER BY a.COMP_ID, a.PROJ_ID, a.BUIL_ID, a.PART_ID, a.id
现在是想删除suite表中多余的重复字段,只保留字段no值最小的字段 drop table #tmp drop table #tmp2 select * into #tmp from suite select min(no) as id1 into #tmp2 from #tmp group by comp_id,proj_id,buil_id,part_id,id delete from suite insert into suite(comp_id,proj_id,buil_id,part_id,id,addid,area,price,discprice,money,type, [use],declared,taxed,sailed,time,paid2,paid,prein,reprein,paid3)
select comp_id,proj_id,buil_id,part_id,id,addid,area,price,discprice,money,type, [use],declared,taxed,sailed,time,paid2,paid,prein,reprein,paid3 from #tmp where no in ( select id1 from #tmp2)
例子
表名:suite
查找suite表中其中5个字段重复的记录,这五个字段分别是comp_id,proj_id,buil_id,part_id,id
查询的SQL代码如下: SELECT *
FROM suite a INNER JOIN
(SELECT comp_id, proj_id, buil_id, part_id, id
FROM suite
GROUP BY comp_id, proj_id, buil_id, part_id, id
HAVING COUNT(*) > 1) b ON a.COMP_ID = b.comp_id AND
a.PROJ_ID = b.proj_id AND a.BUIL_ID = b.buil_id AND a.PART_ID = b.part_id AND
a.id = b.id
ORDER BY a.COMP_ID, a.PROJ_ID, a.BUIL_ID, a.PART_ID, a.id
现在是想删除suite表中多余的重复字段,只保留字段no值最小的字段 drop table #tmp drop table #tmp2 select * into #tmp from suite select min(no) as id1 into #tmp2 from #tmp group by comp_id,proj_id,buil_id,part_id,id delete from suite insert into suite(comp_id,proj_id,buil_id,part_id,id,addid,area,price,discprice,money,type, [use],declared,taxed,sailed,time,paid2,paid,prein,reprein,paid3)
select comp_id,proj_id,buil_id,part_id,id,addid,area,price,discprice,money,type, [use],declared,taxed,sailed,time,paid2,paid,prein,reprein,paid3 from #tmp where no in ( select id1 from #tmp2)
相关阅读 更多 +
