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