得到的序号如下: 
很明显的是重复列都分组分割排序,只需要delete掉排序序号>1的即可.
代码如下:
--用到CTE
WITH DupsD
AS (
SELECT Col1, Col2,
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,
ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rn
FROM Dup1) A WHERE A.rn>1
2.创建一个标识键唯一的表记一列.
代码如下:
ALTER TABLE dbo.Dup1
ADD
PK INT IDENTITY
NOT NULL
CONSTRAINT PK_Dup1 PRIMARY KEY;
SELECT *
FROM Dup1;
删除找出与Col1,Col2相同并且比Dup1.PK大的记录,也就是保留重复值中PK最小的记录.
代码如下:
DELETE Dup1
WHERE EXISTS ( SELECT *
FROM Dup1 AS D1
WHERE D1.Col1 = Dup1.Col1
AND D1.Col2 = Dup1.Col2
AND D1.PK > Dup1.PK );
3.select distant into,这种方法借助一个新的table,把不重复的结果集转移到新table中.
代码如下:
SELECT distinct Col1, Col2 INTO NoDupsFROM Dup1;select * from NoDups
建议采用第一种和第三种方法,第一种多见于T-SQL的编程中,第三种在ETL中常常使用.
