删除Table表中的重复行的方法

复制代码 代码如下:

create table Dup1
(
Col1 int null,
Col2 varchar(20) null
)
insert into Dup1 values
(1, 'aaa'),
(2, 'aaa'),
(2, 'aaa'),
(2, 'aaa'),
(3, 'bbb'),
(3, 'bbb'),
(4, 'ccc'),
(4, 'ddd'),
(5, 'eee')
select * from Dup1

复制代码 代码如下:

SELECT Col1, Col2, COUNT(*) AS DupCountFROM Dup1GROUP BY Col1, Col2HAVING COUNT(*) > 1

复制代码 代码如下:

SELECT Col1, Col2,ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rnFROM Dup1

复制代码 代码如下:

--用到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

复制代码 代码如下:

ALTER TABLE dbo.Dup1
ADD
PK INT IDENTITY
NOT NULL
CONSTRAINT PK_Dup1 PRIMARY KEY;
SELECT *
FROM Dup1;

复制代码 代码如下:

DELETE Dup1
WHERE EXISTS ( SELECT *
FROM Dup1 AS D1
WHERE D1.Col1 = Dup1.Col1
AND D1.Col2 = Dup1.Col2
AND D1.PK > Dup1.PK );

复制代码 代码如下:

SELECT distinct Col1, Col2 INTO NoDupsFROM Dup1;select * from NoDups