Поделиться через

Удаление повторяющихся строк из 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 или другой аналогичный метод.