Udostępnij za pośrednictwem

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

Ten artykuł zawiera skrypt, którego można użyć do usuwania zduplikowanych wierszy z tabeli w Microsoft SQL Server.

Oryginalna wersja produktu: SQL Server
Oryginalny numer KB: 70956

Podsumowanie

Istnieją dwie typowe metody usuwania zduplikowanych rekordów z tabeli SQL Server. Na potrzeby demonstracji rozpocznij od utworzenia przykładowej tabeli i danych:

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 spróbuj użyć następujących metod, aby usunąć zduplikowane wiersze z tabeli.

Metoda 1

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

Następnie spróbuj wykonać poniższe kroki w podanej kolejności:

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

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

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

Metoda 2

Funkcja ROW_NUMBER, która została wprowadzona w Microsoft SQL Server 2005, znacznie ułatwia tę operację:

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

Następnie spróbuj wykonać poniższe kroki w podanej kolejności:

  • Używa ROW_NUMBERfunkcji do partycjonowania danych, na key_value podstawie czego może powstać co najmniej jedna kolumna oddzielona przecinkami.
  • Usuwa wszystkie rekordy, które otrzymały wartość DupRank większą niż 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ć ORDER BY wyrażenia , aby to zrobić.

Więcej informacji

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 elementów i HAVING).
  • Aby uzyskać najlepszą wydajność, należy mieć odpowiedni indeks w tabeli, który używa key_value elementu jako klucza indeksu i zawiera wszystkie kolumny sortowania, które mogły zostać użyte w wyrażeniu ORDER BY .

Jednak ta metoda nie działa w nieaktualnych wersjach programu SQL Server, które nie obsługują funkcji ROW_NUMBER. W takiej sytuacji należy użyć metody 1 lub podobnej metody.