Удаление повторяющихся строк из SQL Server с помощью сценария

  • Статья

В этой статье приведен сценарий, который можно использовать для удаления повторяющихся строк из таблицы в Microsoft SQL Server.

Оригинальная версия продукта: SQL Server
Оригинальный номер базы знаний: 70956

Сводка

Существует два распространенных метода, которые можно использовать для удаления повторяющихся записей из таблицы SQL Server. В демонстрационных целях начните с создания образца таблицы и данных:

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)

Затем попробуйте следующие методы, чтобы удалить повторяющиеся строки из таблицы.

Способ 1

Запустите следующий сценарий:

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

Этот сценарий выполняет следующие действия в указанном порядке:

  • Перемещает один экземпляр любой повторяющейся строки в исходной таблице в дублирующую таблицу.
  • Удаляет все строки из исходной таблицы, которые также находятся в дублирующей таблице.
  • Перемещает строки в дублирующей таблице обратно в исходную таблицу.
  • Удаляет дублирующую таблицу.

Этот метод прост в использовании. Однако для временного создания дублирующей таблицы в базе данных требуется достаточно места. Этот метод также влечет за собой дополнительные затраты, так как данные перемещаются.

Кроме того, если таблица содержит столбец IDENTITY, при восстановлении данных в исходной таблице необходимо использовать SET IDENTITY_INSERT ON.

Способ 2

Функция ROW_NUMBER, представленная в Microsoft SQL Server 2005, значительно упрощает эту операцию:

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

Этот сценарий выполняет следующие действия в указанном порядке:

  • Использует ROW_NUMBER функцию для разделения данных на основе key_value, в роли которого может выступать один или несколько столбцов, разделенных запятыми.
  • Удаляет все записи, которые получили значение DupRank, превышающее 1. Это указывает на то, что записи являются дубликатами.

(SELECT NULL) Из-за выражения скрипт не сортирует секционированные данные по какому-либо условию. Если логика удаления дубликатов требует выбора записей для удаления, а какие — на основе порядка сортировки других столбцов, можно использовать ORDER BY выражение для этого.

Дополнительная информация

Метод 2 прост и эффективен по следующим причинам:

  • Для этого не требуется временно копировать повторяющиеся записи в другую таблицу.
  • Не требуется присоединять исходную таблицу к самой себе (например, с помощью вложенного запроса, который возвращает все повторяющиеся записи с помощью сочетания GROUP BY и HAVING).
  • Для максимальной производительности в таблице должен быть соответствующий индекс, который использует в key_value качестве ключа индекса и содержит все столбцы сортировки, которые вы могли использовать в ORDER BY выражении.

Однако этот метод не работает в устаревших версиях SQL Server, которые не поддерживают функцию ROW_NUMBER. В этой ситуации следует использовать метод 1 или аналогичный метод.