<div class="codebody" id="code51241"> --用到CTE WITH DupsD AS ( SELECT Col1, ROW_NUMBER() OVER (PARTITION BY Col1,Col2 ORDER BY Col1) AS rn FROM Dup1 ) DELETE DupsD WHERE rn > 1; --或者 DELETE A FROM ( SELECT Col1,Col2 ORDER BY Col1) AS rn FROM Dup1) A WHERE A.rn>1
2.创建一个标识键唯一的表记一列. <div class="codetitle"><a style="CURSOR: pointer" data="32678" class="copybut" id="copybut32678" onclick="doCopy('code32678')">代码如下:<div class="codebody" id="code32678"> ALTER TABLE dbo.Dup1 ADD PK INT IDENTITY NOT NULL CONSTRAINT PK_Dup1 PRIMARY KEY; SELECT FROM Dup1;
删除找出与Col1,Col2相同并且比Dup1.PK大的记录,也就是保留重复值中PK最小的记录. <div class="codetitle"><a style="CURSOR: pointer" data="14925" class="copybut" id="copybut14925" onclick="doCopy('code14925')">代码如下:<div class="codebody" id="code14925"> DELETE Dup1 WHERE EXISTS ( SELECT FROM Dup1 AS D1 WHERE D1.Col1 = Dup1.Col1 AND D1.Col2 = Dup1.Col2 AND D1.PK > Dup1.PK );