Удаление дублирующихся записей в SQL Server: TSQL запрос

Быстрый ответ

Чтобы удалить дубликаты из таблицы SQL Server, вы можете воспользоваться общими табличными выражениями (CTE) в сочетании с функцией ROW_NUMBER(). Присвойте каждой строке внутри группы, содержащей дубликаты, уникальный номер и избавьтесь от строк, у которых этот номер больше одного.

Возьмите за основу следующий пример:

-- Время избавиться от дублированных данных!
WITH Dupes AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY DuplicateColumn ORDER BY Id) AS row_num
  FROM
    TableName
)
DELETE FROM Dupes WHERE row_num > 1;

Вместо TableName укажите название вашей таблицы, вместо DuplicateColumn -- наименование столбца с повторяющимися значениями, а вместо Id -- имя столбца с уникальным идентификатором.

Проверка перед удалением

Перед проведением удаления рекомендуется просмотреть записи. Для этого используйте оператор SELECT вместо DELETE:

-- Давайте сначала взглянем на дубликаты.
SELECT * FROM Dupes WHERE row_num > 1;

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

Обработка специфических типов данных

В случае, когда вам приходится иметь дело с бинарными данными или столбцами GUID, посоветуйтесь использовать приведение типов в связке с функцией MIN():

-- Обрабатываем бинарные данные и GUID.
SELECT MIN(CAST(GuidColumn AS BINARY(16))) FROM TableName GROUP BY DuplicateColumn;

Это позволит вам избежать нежелательных сюрпризов при работе с такого рода данными.

Поддержка целостности данных

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

-- Добавим ограничение на уникальность.
ALTER TABLE TableName ADD CONSTRAINT UC_TableName UNIQUE (UniqueColumn);

Таким образом, вы будете избавлены от возникновения будущих проблем с дубликатами.

Эффективная обработка данных

В случае работы с большими объемами данных, вы можете отыскать полезными CTE и оператор DELETE:

-- Используем CTE для управления большими объемами данных.
WITH CTE AS ( /* здесь ваш SQL-код */ )
DELETE FROM CTE WHERE /* здесь условие для удаления */;

Визуализация

Представим, что у вас есть школа, в которой учатся ученики, каждый из которых обладает значком с именем. Некоторые имена повторяются:

Пакет для новичка: [👩‍🎓Емма, 👨‍🎓Ной, 👩‍🎓Емма, 👨‍🎓Лиам, 👩‍🎓Емма]

Сохраним первого ученика с именем "Емма" и удалим последующих:

DELETE e1
FROM students e1
JOIN students e2 
ON e1.name = e2.name AND e1.id > e2.id;

Зона для VIP: [👩‍🎓Емма, 👨‍🎓Ной, 👨‍🎓Лиам]

Таким образом, у каждого из учеников теперь уникальное имя.

Использование самосоединений и NOT IN

Самосоединения и подзапросы с использованием NOT IN тоже оказываются эффективными в борьбе с дубликатами:

-- Используем NOT IN для удаления.
DELETE FROM TableName
WHERE Id NOT IN (
  SELECT MIN(Id) FROM TableName GROUP BY DuplicateColumn
);

Использование этого решения оправдано, когда у вас есть уникальный идентификатор для группировки дубликатов.

Удаление, основанное на условиях эффективности

Оператор MAX() поможет оставить последние записи, предполагая, что данные требуют особой обработки:

-- Оставляем последнюю запись с использованием MAX().
WITH RankedDuplicates AS (
  SELECT
    MAX(Id) OVER (PARTITION BY DuplicateColumn) AS KeepId,
    Id
  FROM
    TableName
)
DELETE FROM TableName
WHERE Id NOT IN (SELECT KeepId FROM RankedDuplicates);

Таким образом, вы сможете сохранять лишь самые последние записи.

Полезные материалы

1. ROW_NUMBER (Transact-SQL) – SQL Server | Microsoft Learn — детальная информация о функции ROW_NUMBER().
2. WITH common_table_expression (Transact-SQL) – SQL Server | Microsoft Learn — всё, что нужно знать о CTE в SQL Server.
3. SQL Server Index Basics – Simple Talk — основы создания индексов в SQL Server.
4. How to delete duplicate rows in SQL Server? – Stack Overflow — обсуждение и практические примеры удаления дубликатов.
5. MERGE (Transact-SQL) – SQL Server | Microsoft Learn — информация о команде MERGE в SQL Server.