多个字段除重,在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