删除表中重复记录

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