Usuwanie zduplikowanych wierszy z tabeli SQL Server przy użyciu skryptu

Oryginalna wersja produktu: SQL Server
Oryginalny numer KB: 70956

Podsumowanie

W tym artykule pokazano, jak usunąć zduplikowane wiersze z tabeli w Microsoft SQL Server przy użyciu skryptu Transact-SQL (T-SQL). Opisuje dwie typowe metody: kopiowanie jednego wystąpienia każdego zduplikowanego wiersza do oddzielnej tabeli i używanie funkcji ROW_NUMBER w celu usunięcia dodatkowych wierszy na miejscu. Użyj tego artykułu, jeśli tabela zawiera powtarzające się rekordy, na przykład ponieważ nie ma unikatowego ograniczenia ani klucza podstawowego. Metoda 2 jest prostsza, ponieważ działa w miejscu.

Utwórz przykładową tabelę zawierającą zduplikowane wiersze

Aby zademonstrować obie metody, utwórz przykładową tabelę o nazwie original_table zawierającą jedną kolumnę , key_valuei dodaj zduplikowane dane:

CREATE TABLE original_table (key_value int )

INSERT INTO original_table values (1)
INSERT INTO original_table values (1)
INSERT INTO original_table values (1)

INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)

Następnie użyj jednej z następujących metod, aby usunąć z tabeli zduplikowane wiersze.

Note

Te skrypty trwale usuwają dane. Przed uruchomieniem ich na danych produkcyjnych należy utworzyć kopię zapasową tabeli lub bazy danych.

Metoda 1. Przenoszenie zduplikowanych wierszy do oddzielnej tabeli i ponowne wstawienie jednej kopii

Metoda 1 używa dwóch tabel: original_table i drugiej tabeli, duplicate_table, którą tworzy skrypt, a następnie ją usuwa. Aby zastosować skrypt do innej tabeli, zastąp skrypt original_table nazwą tabeli. Ta metoda wymaga ponownego zapisania logiki GROUP BY i COUNT, jeśli duplikat jest zdefiniowany przez więcej niż jedną kolumnę.

Uruchom następujący skrypt:

SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1

DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)

INSERT original_table
SELECT *
FROM duplicate_table

DROP TABLE duplicate_table

Ten skrypt wykonuje poniższe kroki w podanej kolejności:

  • Przenosi jedno wystąpienie dowolnego zduplikowanego wiersza w oryginalnej tabeli do tabeli duplikatów.
  • Usuwa wszystkie wiersze z oryginalnej tabeli, które znajdują się również w zduplikowanej tabeli.
  • Przenosi wiersze ze zduplikowanej tabeli z powrotem do oryginalnej tabeli.
  • Usuwa zduplikowaną tabelę.

Ta metoda jest prosta. Wymaga jednak wystarczającej ilości miejsca w bazie danych, aby tymczasowo utworzyć zduplikowaną tabelę. Ta metoda wiąże się również z obciążeniem, ponieważ przenosisz dane.

Ponadto jeśli tabela ma kolumnę IDENTITY , podczas przywracania danych do oryginalnej tabeli należy użyć polecenia SET IDENTITY_INSERT ON .

Metoda 2. Używanie funkcji ROW_NUMBER do usuwania zduplikowanych wierszy

Funkcja ROW_NUMBER, która została wprowadzona w Microsoft SQL Server 2005 r., sprawia, że ta operacja jest znacznie prostsza. Zastąp key_value kolumną lub kolumnami, które definiują duplikat.

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY key_value
              ORDER BY (SELECT NULL)
            )
FROM original_table
) AS T
WHERE DupRank > 1

Ten skrypt wykonuje poniższe kroki w podanej kolejności:

  • Używa funkcji ROW_NUMBER do partycjonowania danych na podstawie key_value, które może zawierać co najmniej jedną kolumnę oddzieloną przecinkami.
  • Usuwa wszystkie rekordy, które otrzymały wartość większą niż DupRank 1. Ta wartość określa, że rekordy są duplikatami.

Ze względu na (SELECT NULL) wyrażenie skrypt nie sortuje partycjonowanych danych na podstawie żadnego warunku. Jeśli logika usuwania duplikatów wymaga wybrania rekordów do usunięcia i zachowania na podstawie kolejności sortowania innych kolumn, możesz użyć wyrażenia ORDER BY, aby to zrobić.

Zachowaj najnowszy wiersz podczas usuwania duplikatów

Aby zachować wiersz z najnowszą wartością, posortuj w kolejności malejącej według tej kolumny. Rozważmy na przykład hipotetyczny employee_table, który ma więcej niż jeden wiersz dla tego samego pracownika. Poniższy skrypt zachowuje tylko wiersz, który ma najnowszą wartość updated_date:

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY employee_name
              ORDER BY updated_date DESC
            )
FROM employee_table
) AS T
WHERE DupRank > 1

Wiersz, który ma najnowszą updated_date wartość, otrzymuje DupRank wartość 1, więc skrypt przechowuje go i usuwa starsze wiersze.

Metoda 2 jest prosta i skuteczna z następujących powodów:

  • Nie wymaga tymczasowego skopiowania zduplikowanych rekordów do innej tabeli.
  • Nie wymaga sprzężenia oryginalnej tabeli z samą sobą (na przykład przy użyciu podzapytania zwracającego wszystkie zduplikowane rekordy przy użyciu kombinacji GROUP BY i HAVING).
  • Aby uzyskać najlepszą wydajność, należy mieć odpowiedni indeks w tabeli, który używa key_value jako klucza indeksu i zawiera wszystkie kolumny sortowania, które mogły zostać użyte w wyrażeniu ORDER BY.

Funkcja ROW_NUMBER jest dostępna w SQL Server 2005 i nowszych wersjach. Jeśli używasz wersji SQL Server, która nie obsługuje funkcji ROW_NUMBER, użyj metody 1 lub podobnej metody.

Jak mogę zapobiec zduplikowaniu wierszy w tabeli?

Po usunięciu duplikatów można zapobiec tworzeniu nowych zduplikowanych wierszy, definiując ograniczenie UNIQUE lub unikatowy indeks dla kolumny lub kolumn identyfikujących wiersz.

Treści powiązane

  • Ostatnia aktualizacja o