USE AdventureWorks
GO
IF EXISTS(SELECT * FROM sys.tables 
          WHERE name='Duplicate')
   DROP TABLE Duplicate
GO
CREATE TABLE Duplicate 
(  uname varchar(10),
   udt datetime default(getdate()) --該資料行會自動產生建檔日期時間
)
GO

INSERT INTO Duplicate VALUES('LEWIS',DEFAULT)
INSERT INTO Duplicate VALUES('LEWIS',DEFAULT)
INSERT INTO Duplicate VALUES('LEWIS',DEFAULT)

INSERT INTO Duplicate VALUES('ADA',DEFAULT)
INSERT INTO Duplicate VALUES('ADA',DEFAULT)


SELECT * FROM Duplicate

WHILE 1=1
BEGIN
  DELETE TOP(1) FROM Duplicate
  WHERE uname IN 
        (SELECT uname FROM Duplicate
         GROUP BY uname
         HAVING COUNT(*)>1 )
  IF @@ROWCOUNT=0 BREAK
END