sqlserver删除重复数据(有部分数据重复，id不同，其他相同)

select * from 
(
select a.* from VI_CIC_PolicyCover a,VI_CIC_PolicyCover b 
where a.PolicyNO=b.PolicyNo 
and a.InsuredId=b.InsuredId
and a.CoverName=b.CoverName
and a.InsuredAmount=b.InsuredAmount 
and a.PremiumAmount=b.PremiumAmount 
and a.Id <>b.Id
) c;

with cte as
(
select * from VI_CIC_PolicyCover a where id <> 
(select id from VI_CIC_PolicyCover b 
where a.PolicyNO=b.PolicyNo 
and a.InsuredId=b.InsuredId
and a.CoverName=b.CoverName
and a.InsuredAmount=b.InsuredAmount 
and a.PremiumAmount=b.PremiumAmount 
and a.Id <>b.Id) 
)
cte2 as
(
可以引用cet1里面的内容
)
cte3 as
(
可以引用cet1，cet1里面的内容
)

select PolicyNO,InsuredId,CoverName,max(id) as Id into #temp from cte group by PolicyNO,InsuredId,CoverName

上面所有的语句要一起执行 

 

 

创建临时表，取重复数据其中一半的id

select * from #temp

删除：

delete from VI_CIC_PolicyCover where id in (
select id from #temp
)