روش‌هايي براي حذف ركوردهاي تكراري

هر برنامه نويسي در طول عمر كاري خود حداقل يكبار با اين مساله مواجه خواهد شد: "چگونه يك سري ركورد تكراري موجود را بايد حذف كرد؟"

ابتدا جدول موقتي زير را كه در آن يك سري ركورد تكراري ثبت شده است در نظر بگيريد:

CREATE TABLE #Employee
(
ID          INT,
FIRST_NAME  NVARCHAR(100),
LAST_NAME   NVARCHAR(300)
)

INSERT INTO #Employee VALUES ( 1, 'Vahid', 'Nasiri' );
INSERT INTO #Employee VALUES ( 2, 'name1', 'lname1' );
INSERT INTO #Employee VALUES ( 3, 'name2', 'lname2' );
INSERT INTO #Employee VALUES ( 2, 'name1', 'lname1' );
INSERT INTO #Employee VALUES ( 3, 'name2', 'lname2' );
INSERT INTO #Employee VALUES ( 4, 'name3', 'lname3' );

روش‌هاي حذف ركوردهاي تكراري جدول موقتي Employee

الف) استفاده از يك جدول موقتي ديگر بر اساس تمامي فيلدهاي موجود

SELECT DISTINCT *
FROM   #Employee 
   
SELECT * INTO #DuplicateEmployee
FROM   #Employee

INSERT #DuplicateEmployee
SELECT DISTINCT *
FROM   #Employee

BEGIN TRAN 
DELETE #Employee
INSERT #Employee
SELECT *
FROM   #DuplicateEmployee

   COMMIT TRAN 
    
  DROP TABLE #DuplicateEmployee 
   
SELECT DISTINCT *
FROM   #Employee 

ب) استفاده از يك جدول موقتي ديگر بر اساس يك مجموعه از فيلدهاي موجود

SELECT DISTINCT * FROM #Employee

SELECT * INTO #DuplicateEmployee FROM #Employee

INSERT #DuplicateEmployee
SELECT ID,
    FIRST_NAME,
    LAST_NAME
FROM   #Employee
GROUP BY
    ID,
    FIRST_NAME,
    LAST_NAME
HAVING COUNT(*) > 1

BEGIN TRAN
DELETE #Employee
FROM   #DuplicateEmployee
WHERE  #Employee.ID = #DuplicateEmployee.ID
   AND #Employee.FIRST_NAME = #DuplicateEmployee.FIRST_NAME
   AND #Employee.LAST_NAME = #DuplicateEmployee.LAST_NAME

INSERT #Employee
SELECT *
FROM   #DuplicateEmployee

COMMIT TRAN
DROP TABLE #DuplicateEmployee

SELECT DISTINCT * FROM   #Employee

ج) استفاده از rowcount بر اساس يك مجموعه از فيلدهاي موجود

SELECT DISTINCT *
FROM   #Employee

SET ROWCOUNT 1
SELECT 1
WHILE @@rowcount > 0
   DELETE #Employee
   WHERE  1 < (
              SELECT COUNT(*)
              FROM   #Employee a2
              WHERE  #Employee.ID = a2.ID
                     AND #Employee.FIRST_NAME = a2.FIRST_NAME
                     AND #Employee.LAST_NAME = a2.LAST_NAME
          )

SET ROWCOUNT 0

SELECT DISTINCT *
FROM   #Employee

د) استفاده از Analytical Functions بر اساس يك مجموعه از فيلدهاي موجود

SELECT DISTINCT *
FROM   #Employee;
 
WITH #DeleteEmployee AS (
                         SELECT ROW_NUMBER()
                                OVER(PARTITION BY ID, First_Name, Last_Name ORDER BY ID) AS
                                RNUM
                         FROM   #Employee
                     )

DELETE
FROM   #DeleteEmployee
WHERE  RNUM > 1

SELECT DISTINCT *
FROM   #Employee

ه) استفاده از يك فيلد identity جديد بر اساس يك مجموعه از فيلدهاي موجود

SELECT DISTINCT *
FROM   #Employee;
 
ALTER TABLE #Employee ADD UNIQ_ID INT IDENTITY(1, 1)

DELETE
FROM   #Employee
WHERE  UNIQ_ID < (
        SELECT MAX(UNIQ_ID)
        FROM   #Employee a2
        WHERE  #Employee.ID = a2.ID
               AND #Employee.FIRST_NAME = a2.FIRST_NAME
               AND #Employee.LAST_NAME = a2.LAST_NAME
    )

ALTER TABLE #Employee DROP COLUMN UNIQ_ID

SELECT DISTINCT *
FROM   #Employee

و در آخر

DROP TABLE #Employee