SQL中删除重复记录只保留一条的语句

多个字段除重，在ms sql中，删除的表名不能重命名，在oracle中可以

方法一：字段拼起来比较

DELETE from Sap_MG2MG3Changes

WHERE (

ISNULL([Type],'')+ISNULL(RPT_MATERIAL,'')+ISNULL(ChgValueOld,'')+ISNULL(ChgValueNew,'')+ISNULL(update_flag,'')

IN

(

select ISNULL([Type],'')+ISNULL(RPT_MATERIAL,'')+ISNULL(ChgValueOld,'')+ISNULL(ChgValueNew,'')+ISNULL(update_flag,'')

from Sap_MG2MG3Changes

group by ISNULL([Type],'')+ISNULL(RPT_MATERIAL,'')+ISNULL(ChgValueOld,'')+ISNULL(ChgValueNew,'')+ISNULL(update_flag,'')

having count(*) > 1

)

AND

ID NOT IN

(

select MAX(ID)

from Sap_MG2MG3Changes

group by ISNULL([Type],'')+ISNULL(RPT_MATERIAL,'')+ISNULL(ChgValueOld,'')+ISNULL(ChgValueNew,'')+ISNULL(update_flag,'')

having count(*) > 1

)

)

方法二：数据分区取数 OVER (PARTITION BY…)

DELETE t FROM

(

SELECT *,ROW_NUMBER() OVER(PARTITION BY [Type],RPT_MATERIAL,ChgValueOld,ChgValueNew,update_flagORDER BY ID ASC) AS RowID

from Sap_MG2MG3Changes

) t

WHERE t.RowID>1;

方法三：

delete from

crm.dbo.[CT_CustomerSalesAreaTemp]

whereexists (

selecttop 1 * from

( select count(*) countRows,CustomerCode,SalseOrgnization,DistributionChannel,DistributionDivision,MAX(CustomerSaleAreaID)mid

from crm.dbo.[CT_CustomerSalesAreaTemp]

group by CustomerCode,SalseOrgnization ,DistributionChannel,DistributionDivision

) t

where [CT_CustomerSalesAreaTemp].CustomerCode = t.CustomerCode

and [CT_CustomerSalesAreaTemp].SalseOrgnization = t.SalseOrgnization

and [CT_CustomerSalesAreaTemp].DistributionChannel = t.DistributionChannel

and [CT_CustomerSalesAreaTemp].DistributionDivision = t.DistributionDivision

and countRows>1

and [CT_CustomerSalesAreaTemp].CustomerSaleAreaID <t.mid

)

and [CT_CustomerSalesAreaTemp].IsUpdatedFlag=0