我
asked this question a while back删除基于列的重复记录.答案很棒：
delete from tbl where id NOT in ( select min(id) from tbl group by sourceid )
我现在有一个模拟的情况,但重复记录的定义是基于多个列.如何更改此上述sql以识别重复记录,其中唯一记录定义为从Col1 Col2 Col3连接.我会做这样的事情吗？
delete from tbl where id NOT in ( select min(id) from tbl group by col1,col2,col3 )
解决方法
这显示您要保留的行：
;WITH x AS
(
SELECT col1,col3,rn = ROW_NUMBER() OVER
(PARTITION BY col1,col3 ORDER BY id)
FROM dbo.tbl
)
SELECT col1,col3 FROM x WHERE rn = 1;
;WITH x AS ( SELECT col1,col3 FROM x WHERE rn > 1;
一旦你高兴,上述两套是正确的,以下将实际删除它们：
;WITH x AS ( SELECT col1,col3 ORDER BY id) FROM dbo.tbl ) DELETE x WHERE rn > 1;
