查询并删除多个字段重复记录

查找表中多余的重复记录（多个字段）
例子
表名: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)