假设没有空值，您GROUP BY唯一的列，并选择最小(或最大)RowId作为要保留的行。然后，删除所有没有行id的内容:

DELETE FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

如果您使用的是GUID而不是整数，则可以进行替换

MIN(RowId)

with

CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))

首先，您可以使用MIN()和Group By选择最小RowId。我们将保留这些行。

   SELECT MIN(RowId) as RowId
   FROM MyTable 
   GROUP BY Col1, Col2, Col3

删除那些不在最小RowId使用范围内的RowId

DELETE FROM MyTable WHERE RowId Not IN()

最后的查询:

DELETE FROM MyTable WHERE RowId Not IN(

    SELECT MIN(RowId) as RowId
    FROM MyTable 
    GROUP BY Col1, Col2, Col3
)

你也可以在SQL Fiddle中检查我的答案

另一种表达方式:——

DELETE A
FROM   TABLE A,
       TABLE B
WHERE  A.COL1 = B.COL1
       AND A.COL2 = B.COL2
       AND A.UNIQUEFIELD > B.UNIQUEFIELD 

通过使用下面的查询，我们可以基于单列或多列删除重复的记录。下面的查询是基于两列进行删除。表名为:testing，列名为empno,empname

DELETE FROM testing WHERE empno not IN (SELECT empno FROM (SELECT empno, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) 
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)
or empname not in
(select empname from (select empname,row_number() over(PARTITION BY empno ORDER BY empno) 
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)

另一个简单的解决方案可以在这里粘贴的链接中找到。这个方法很容易掌握，似乎对大多数类似的问题都很有效。虽然它是为SQL Server，但所使用的概念是可以接受的。

以下是链接页面的相关部分:

考虑以下数据:

EMPLOYEE_ID ATTENDANCE_DATE
A001    2011-01-01
A001    2011-01-01
A002    2011-01-01
A002    2011-01-01
A002    2011-01-01
A003    2011-01-01

那么我们如何删除这些重复的数据呢?

首先，使用以下代码在表中插入一个标识列:

ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)  

使用下面的代码来解决它:

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
    FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 

DELETE 
FROM MyTable
WHERE NOT EXISTS (
              SELECT min(RowID)
              FROM Mytable
              WHERE (SELECT RowID 
                     FROM Mytable
                     GROUP BY Col1, Col2, Col3
                     ))
               );