在工作和麵試中，經常出現如何查詢或者刪除重複資料的問題，如果有主鍵，那還好辦一點，如果沒有主鍵，那就有點麻煩。

         當一個表上沒有輔助鍵時，如果使用SSMS介面來刪除，就會報錯（注意，本人測試環境是2012，所以介面可能會有所不一樣，但是對結果沒有任何影響）：

         因為在建立表後插入資料是沒有做判斷。但是在刪除時，為了保證資料庫的一致性，RDBMS還是會做判斷從而拒絕執行這類操作。

說明：

         要解決這種問題，除了在設計的過程中做好之外，還可以在資料沒有重複資料的情況下，使用ALTER TABLE ADD Constraint語句來增加約束。

         但是要刪除現有的重複值，使用SSMS介面是無法實現的，就算能實現，當資料量到達一定程度，也是不現實的。此時只能使用T-SQL語句，搭配SET ROWCOUNT 1讓資料的處理方式一次一行或這使用DELETE  TOP (1)的方式刪除，注意，括號是必須的。

         SET ROWCOUNT { number | @number_var }：使 SQL Server 在返回指定的行數之後停止處理查詢。如果需要取消限制，只需要使用SETROWCOUNT 0就可以。

       下面是例子：

       --使用set rowcount 3設定，查詢所有資料

SET ROWCOUNT  3

SELECT * FROM AdventureWorks.HumanResources.Department

--結果僅返回3行

         結果如下：

--使用set rowcount 3，修改所有資料

UPDATE AdventureWorks.HumanResources.Department

SET name=name

(3 行受影響)

--回覆原有設定

SET ROWCOUNT  0

 

最後使用TOP (N)設定搭配INSERT/UPDATE /DELETE ，注意這部分只適合2005以後。

--使用TOP(3)設定，查詢所有資料，注意是要有括號的

SELECT TOP(3) * FROM AdventureWorks.HumanResources.Department

--結果返回3行

 

UPDATE TOP(3) AdventureWorks.HumanResources.Department SET name =name

(3 行受影響)

 

另外，SQLServer提供了一個系統函式@@ROWCOUNT來返回影響行數。以下是例子：

--使用@@rowcount系統函式返回影響行數

SELECT EmployeeID,Title

FROM AdventureWorks.HumanResources.Employee

WHERE Title LIKE '%Manager%'

GO

SELECT @@ROWCOUNT 'Result'

解決方法：

首先建立一個測試表和插入測試資料：

USE tempdb

GO

CREATE TABLE MyT

(

    [SID]   INT,

    sname   VARCHAR(10),

    sdt     DATETIME

)

GO

--插入測試資料

INSERT INTO MyT VALUES (1,'Lu','2012/01/01');

INSERT INTO MyT VALUES (1,'Lu','2012/07/08');

INSERT INTO MyT VALUES (1,'Lu','2012/04/03');

INSERT INTO MyT VALUES (2,'Tian','2012/03/01');

INSERT INTO MyT VALUES (2,'Tian','2012/05/09');

INSERT INTO MyT VALUES (2,'Tian','2012/01/01');

INSERT INTO MyT VALUES (3,'AD','2012/01/08');

INSERT INTO MyT VALUES (3,'AD','2012/03/01');

INSERT INTO MyT VALUES (4,'Sun','2012/02/01');

INSERT INTO MyT VALUES (1,'Lu','2012/01/01');

INSERT INTO MyT VALUES (1,'Lu','2012/07/08');

INSERT INTO MyT VALUES (1,'Lu','2012/04/03');

INSERT INTO MyT VALUES (2,'Tian','2012/03/01');

INSERT INTO MyT VALUES (2,'Tian','2012/05/09');

INSERT INTO MyT VALUES (2,'Tian','2012/01/01');

INSERT INTO MyT VALUES (3,'AD','2012/01/08');

INSERT INTO MyT VALUES (3,'AD','2012/03/01');

INSERT INTO MyT VALUES (4,'Sun','2012/02/01');

GO

 

第一種方法：

使用SET ROWCOUNT 1方法來刪除重複資料：

需要搭配WHILE 1=1無限迴圈，搭配BREAK作為終止。針對找出來的重複資料，使用GROUP BY 和HAVING COUNT(1)>1作為篩選條件，可以避免所有資料被刪除。

SET ROWCOUNT 1

WHILE 1=1

BEGIN

    DELETE FROM MyT

    WHERE [sid] IN

    (

        SELECT [sid] FROM MyT

        GROUP BY [sid],sname

        HAVING COUNT(1)>1

    )

    IF @@ROWCOUNT=0

    BREAK

END

SET ROWCOUNT 0

 

--可以發現，重複的資料已經刪除

SELECT * FROM MyT

第二種方法：

使用DELETE TOP(N)方法，先把剛才插入測試資料的指令碼再執行，可以多執行幾次。DELETE TOP(1)可以用來替代SET ROWCOUNT 1：

WHILE 1=1

BEGIN

    DELETE TOP(1) FROM MyT

    WHERE [sid] IN

    (

        SELECT [sid] FROM MyT

        GROUP BY [sid],sname

        HAVING COUNT(1)>1

    )

    IF @@ROWCOUNT=0

    BREAK

END

 

結果和上面的一樣。

 

擴充：保留最近的一行資料：

有時候不僅僅要去掉重複資料，也要保證剩下的是最新的資料（日期最大），此時可以藉助索引，使用索引排序，然後把日期最小的那些刪掉，只保留日期最大的那一筆。

--建立複合索引，利用索引將資料以編號和日期升序排序

CREATE INDEX IDX_DT ON MyT([sid],sdt ASC)

GO

--修改刪除語句，搭配with index查詢提示

WHILE 1=1

BEGIN

    DELETE TOP(1) FROM MyT

    WHERE [sid] IN

    (

        SELECT [sid] FROM MyT WITH (INDEX(idx_dt))

        GROUP BY [sid],sname

        HAVING COUNT(1) >1

    )

    IF @@ROWCOUNT=0

    BREAK

END

 

查詢結果：

SELECT * FROM MyT

 

注意：

為了向後相容，括號在 SELECT 語句中是可選的。

我們建議您始終對 SELECT 語句中的 TOP 使用括號，這樣，就可以與在 INSERT、UPDATE、MERGE 和 DELETE 語句中需要使用括號保持一致（在這種情況下括號是必需的）。

（出自SQL SERVER 2012聯機叢書）