刪除表中重複記錄

標籤：ar   table   rom   記錄   select   value   ide   create   it   

create table myTable1
(
id int primary key identity,
col1 int,
col2 int
)

DECLARE @i int;
set @i=0;
WHILE @i<10
BEGIN
INSERT INTO myTable1 VALUES(@i,@i);
INSERT INTO myTable1 VALUES(@i,@i);
SET @i+=1;
End

--找到重複的記錄
SELECT col1,col2 from myTable1 GROUP BY col1,col2 HAVING COUNT(*)>1

--找到重複的記錄（col2和col1全部是一樣的才是重複），並刪掉重複記錄
DELETE from mytable1 WHERE col1 IN (SELECT col1 from myTable1 GROUP BY col1,col2 HAVING COUNT(*)>1)
AND id NOT IN (SELECT MIN(id) from myTable1 GROUP BY col1,col2 HAVING COUNT(*)>1 )

SELECT *from myTable1

刪除表中重複記錄