Как удалить дубликаты строк из таблицы?

Моисеенко С.И. (13-06-2009)

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

Пусть имеется следующая таблица T:

Для простоты я не включаю сюда другие столбцы, предполагая, что данные в них однозначно определяются значением в столбце name. Требуется сделать столбец name уникальным (скажем, первичным ключом), предварительно удалив дубликаты.

Распространенным решением данной проблемы является создание вспомогательной таблицы требуемой структуры, в которую копируются уникальные строки из таблицы T с последующим удалением таблицы T и переименованием вспомогательной таблицы. Ниже приводится код на языке T-SQL, реализующий данный алгоритм.

CREATE TABLE Ttemp(name VARCHAR(50) NOT NULL PRIMARY KEY);

GO

INSERT INTO Ttemp

SELECT DISTINCT * FROM T;

GO

DROP TABLE T;

GO

EXEC sp_rename 'Ttemp', 'T';

GO

В результате получим то, что и требовалось:

При этом ограничение первичного ключа будет препятствовать появлению дубликатов впоследствии.

А можно ли обойтись без создания новой таблицы? Можно. Например, с помощью такого алгоритма:
- добавить новый столбец типа счетчик (IDENTITY), который перенумерует все имеющиеся строки в таблице;
- из каждой группы строк с одинаковым значением в столбце name удалить все строки за исключением строки с максимальным номером (или минимальным - это все равно, т.к. мы имеем дело с дубликатами);
- удалить вспомогательный столбец;
- наложить ограничение.

Вот пример реализации такого подхода:

ALTER TABLE T

ADD id INT IDENTITY(1,1);

GO

DELETE FROM T

WHERE id < (SELECT MAX(id)

FROM T AS T1

WHERE T.name = T1.name

);

GO

ALTER TABLE T

DROP COLUMN id;

GO

ALTER TABLE T

ALTER COLUMN name VARCHAR(50) NOT NULL;

GO

ALTER TABLE T

ADD CONSTRAINT T_PK PRIMARY KEY(name);

А если без создания дополнительного столбца? Опять ответ утвердительный, но тут нам потребуются новые возможности языка, специфицированные в стандарте ANSI SQL-99. Идея состоит в том, чтобы создавать не постоянный столбец в таблице, который потом потребуется удалять, а виртуальный (вычисляемый). Этот столбец мы создадим с помощью оконных функций, присвоив ранг каждой строке внутри окна, определяемого равенством значений в столбце name. Наконец, мы удалим все строки с рангом выше 1.

Давайте подробно рассмотрим построение запроса на удаление дубликатов этим методом.