查询重复项以及删除重复项

–查询出重复项。Deptid,catecode,pid为联合主键。可以换成要查询的内容
select Deptid,catecode,pid,COUNT(0) as num,Max(DepVsSzID) as maxid from EB_DeptVsCostCategory with (nolock) group by
Deptid,catecode,pid having count(0)>1

–创建临时表
create table #Tmp –创建临时表#Tmp
(
Deptid int ,
catecode varchar(50),
pid int,
DepVsSzID int ,
num int
);

–把查找到的数据插入到临时表
insert into #Tmp(Deptid,catecode,pid,num,DepVsSzID)
select Deptid,catecode,pid,COUNT(0) as num,Max(DepVsSzID) as maxid from EB_DeptVsCostCategory with (nolock) group by
Deptid,catecode,pid having count(0)>1

–delete 的多表联合删除
delete EB_DeptVsCostCategory from EB_DeptVsCostCategory, #Tmp p where p.Deptid=EB_DeptVsCostCategory.Deptid and p.catecode=EB_DeptVsCostCategory.catecode
and p.pid=EB_DeptVsCostCategory.pid and EB_DeptVsCostCategory.DepVsSzID<>p.DepVsSzID

–删除临时表及数据
Select COUNT(0) as num from #Tmp –查询临时表的数据
truncate table #Tmp –清空临时表的所有数据和约束
drop table #Tmp