Как убрать повторы в sql

Простой и эффективный метод удаления дубликатов из таблицы

Как быстро и просто удалить дубликаты данных в SQL-базе, чтобы избежать ошибок в программном коде, который использует эти данные.

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

В этой короткой статье я хочу поделиться простым способом удаления дубликатов из таблицы. Запрос работает в базах данных MySQL, MariaDB и PostgreSQL. Если вам интересен такой запрос для других СУБД, напишите мне в комментариях.

Все вышеизложенные а также любые другие запросы можно воспроизвести на SQLize.online – онлайн редакторе SQL.

Давайте начнем. Предположим, у нас есть простая таблица с двумя столбцами: id – это первичный ключ и v простое целочисленное значение

create table tbl ( id int primary key, val int ); insert into tbl (id, val) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 1), (6, 1), (7, 2), (8, 3), (9, 2), (10, 4), (11, 3); 

Приведенный выше код создает таблицу и вставляет несколько значений. Выведем на экран все строки из нашей тестовой таблицы. Как видите, id имеет уникальные значения, но поле val имеет содержит дубликаты:

SELECT * FROM tbl; +====+===+ | id | v | +====+===+ | 1 | 1 | | 2 | 1 | | 3 | 2 | | 4 | 2 | | 5 | 1 | | 6 | 1 | | 7 | 2 | | 8 | 3 | | 9 | 2 | | 10 | 4 | | 11 | 3 | +----+---+ 

Наша задача состоит в том, чтобы удалить строки с поввторяющимися значениями в столбце val и сохранить уникальные значения с минимальным значением идентификатора id.

Для начала попробуем найти дубликаты. Мы можем использовать простое LEFT JOIN таблицы самой с собой по полю val с дополнительным условием для предотвращения объединения идентичных строк (для наглядности дадим алиасы для таблицы и копии):

select * from tbl source_tbl left join tbl copy_tbl on source_tbl.val = copy_tbl.val and source_tbl.id > copy_tbl.id; 

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

+====+===+========+========+ | id | val | id | val | +====+===+========+========+ | 1 | 1 | (null) | (null) | | 2 | 1 | 1 | 1 | | 3 | 2 | (null) | (null) | | 4 | 2 | 3 | 2 | | 5 | 1 | 1 | 1 | | 5 | 1 | 2 | 1 | | 6 | 1 | 1 | 1 | | 6 | 1 | 2 | 1 | | 6 | 1 | 5 | 1 | | 7 | 2 | 3 | 2 | | 7 | 2 | 4 | 2 | | 8 | 3 | (null) | (null) | | 9 | 2 | 3 | 2 | | 9 | 2 | 4 | 2 | | 9 | 2 | 7 | 2 | | 10 | 4 | (null) | (null) | | 11 | 3 | 8 | 3 | +----+---+--------+--------+ 

Видно, что уникальные строки с минимальным id имеют (null) в столбцах из copy_tbl. Это происходит потому что для минимального значения id в исходной таблице нет строк соответсвующих условию в таблице копии. Итак, нам нужно оставить строки в которых нет соответствия (уникальные) и удалить. Это можно осуществить следующим запросом:

delete tbl.* from tbl left join t copy_tbl on tbl.val = copy_tbl.val and tbl.id > copy_tbl.id where copy_tbl.id is not null; 

P.S. Уже после написания этой статьи мой коллега @Akina предложил более короткую версию:

delete tbl.* from tbl join t copy_tbl on tbl.val = copy_tbl.val and tbl.id > copy_tbl.id; 

Если Вам понравилась статья, Вы можете поддержать автора.

SQL-запрос на вывод повторяющихся строк таблицы

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

Предположим, есть таблица TableName, содержащая некое поле DuplicateField. Нужно выбрать все строки, содержащие дублирующиеся значения поля DuplicateField и их количество. Т.е. выбрать те строки, в которых в поле записано одно и тоже значение более одного раза. Тогда запрос на выбор дублирующихся строк для поля DuplicateField может выглядеть так:

SELECT DuplicateField, count(DuplicateField) FROM TableName GROUP BY DuplicateField HAVING count(DuplicateField)>1;

Чтобы выбрать конкретные дублирующиеся элементы, содержащие только значение @SomeValue (параметр), можно добавить условие «WHERE»:

SELECT DuplicateField, count(DuplicateField) FROM TableName WHERE DuplicateField=@SomeValue GROUP BY DuplicateField HAVING count(DuplicateField)>1;

Если нужно провести выборку по нескольким повторяющимся полям, то sql-запрос может выглядеть так:

SELECT DuplicateField1, DuplicateField2 FROM TableName GROUP BY DuplicateField1, DuplicateField2 HAVING (count(DuplicateField2)>1 AND count(DuplicateField2)>1);

И, наконец, если нужно вывести полные строки, содержащие все поля (выбрать *), в которых дублируется одно поле, сделать это можно таким sql-запросом:

SELECT * FROM TableNeme WHERE DuplicateField IN ( SELECT DuplicateField, count(DuplicateField) FROM TableName GROUP BY DuplicateField HAVING count(DuplicateField)>1 );

Если нужно выбрать все поля и строки таблицы, в которых в разных строках повторяются комбинации значений двух и более полей, то sql-запрос будет немного сложнее. Для sql-сервера (Transact-SQL), с применением логического оператора Exists, запрос sql может быть таким :

SELECT * FROM TableName t1 WHERE EXISTS ( SELECT DuplicateField1, DuplicateField2 FROM TableName t2 WHERE t1.DuplicateField1=t2.DuplicateField1 AND t1.DuplicateField1 = t2.DuplicateField2 GROUP BY DuplicateField1, DuplicateField2 HAVING (count(DuplicateField2)>1 AND count(DuplicateField2)>1) )

Как избавиться от дублей в базе данных (на примере MS SQL)

Всем привет! Меня зовут Евгений, я занимаюсь разработкой и проектированием в Ozon. Больше всего работаю с MS SQL и C#, но попадаются и другие СУБД и языки программирования.

Ozon как продукт быстро растёт: во втором квартале этого года мы доставляли больше миллиона посылок в день. Для обработки такого объёма заказов мы используем разные языки и платформы: .NET (C#), Go, MS SQL Server и PostgreSQL.

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

Я расскажу об одном таком случае, когда наша команда потратила много времени и сил, но всё-таки нашла оптимальный способ решения проблемы дублирования данных.

Но сначала позвольте погрузить вас немного в предметную область — объясню, на примере чего будет демонстрироваться проблема дублирования данных, и освещу некоторые методы её решения.

1. расскажу немного о специфике предметной области;
2. рассмотрим популярные варианты борьбы с дублированием данных;
3. опишу, в чём заключается наш способ;
4. покажу реализацию;
5. приведу пример, как это всё работает.

Примеры будут приведены для MS SQL Server. Однако аналогичное решение можно реализовать на любой другой СУБД с учётом её особенностей.

Предметная область: логистика заказов

У нас в Ozon все заказы делятся по отправлениям:

В отправлении может быть один или несколько типов товаров.

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

Заказы бывают двух типов:

1. Одноместный заказ, состоящий из одного отправления (Упаковка 1 — одно отправление):
2. Многоместный заказ, состоящий из двух и более отправлений (Упаковка 1 — одно отправление, Упаковка 2 — второе отправление):

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

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

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

Проблема дублирования данных возникает в отношении многоместных заказов.

Сумма тарификации по бизнес-требованиям должна проставляться целиком только на одном отправлении одного типа тарификации в рамках заказа (выдача или возврат).

И тут мы подходим к сути проблемы. Как заставить асинхронные процессы из других систем Ozon обновлять сумму только в рамках одного типа тарификации, то есть на одном отправлении? Если не делать синхронизацию изменений, то можно получить дублирование сумм для разных отправлений заказа по одному и тому же типу тарификации, то есть когда на одном или нескольких отправлениях в рамках одного заказа будут проставлены одни и те же суммы.

Выбираем метод борьбы с дублированием данных

На практике я встречал два основных способа не допустить дублирования данных:

1. Навесить ограничение на уникальность для нужной комбинации полей в таблице и обрабатывать исключения при нарушении этого ограничения (хорошо работает при вставках и обновлениях данных).
2. Поместить участок кода, где может возникнуть дублирование данных, в транзакцию с уровнем изоляции «сериализуемая» (SERIALIZABLE) (хорошо работает при добавлениях и обновлениях в монопольном режиме).

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

А что, если сделать гибридный вариант?

И тут мы подходим непосредственно к алгоритму борьбы с дублированием данных в нашей задаче.

• На самом деле можно делать пересчёт и постфактум (то есть жить с дублирующими суммами), но в нашем случае это плохое решение, так как разные сервисы будут считывать невалидные данные до момента пересчёта, что приведёт к некорректным счетам;
• мы используем уровень изоляции транзакций снимками (SNAPSHOT).

Алгоритм предотвращения дублирования данных

Верхнеуровнево алгоритм для многоместных заказов выглядит следующим образом:

1. Создаём таблицу, в которой будет содержаться вся необходимая информация для предотвращения дублирования сумм при обновлениях.
2. Делаем хранимую процедуру, которая будет в монопольном режиме добавлять в новую таблицу запись с проверкой и возвращать статус, отражающий, удалось ли добавить запись или нет (если нет, значит, запись уже была добавлена).
3. Создаём хранимую процедуру для записи суммы тарифа. При каждом проставлении суммы сравниваем её с суммой из новой таблицы и сначала обновляем сумму в новой таблице, если там она была 0:
   • в случае успешного обновления обновляем сумму в операции;
   • если в новой таблице уже указана сумма, отличная от 0, и отправление в обработке отличается от отправления, на котором проставлена сумма (то есть кто-то уже обновил сумму и нам не нужны дубли), обнуляем её;
   • в противном случае ничего не делаем.

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

Создаём таблицу с информацией для предотвращения дублирования сумм при обновлениях

Создадим таблицу dbo.LogisticOrderMultiPostingPrincipalTariff следующим образом:

Определение таблицы dbo.LogisticOrderMultiPostingPrincipalTariff

SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS, NOCOUNT, XACT_ABORT ON; GO SET DEADLOCK_PRIORITY HIGH; GO IF (OBJECT_ID(N'dbo.LogisticOrderMultiPostingPrincipalTariff', N'U') IS NULL) BEGIN CREATE TABLE [dbo].[LogisticOrderMultiPostingPrincipalTariff] ( [LogisticOrderID] int NOT NULL , [TariffTypeID] int NOT NULL , [ArticleID] int NOT NULL , [OperationID] int NOT NULL , [Amount] money NOT NULL , [InsertUTCDate] datetime NOT NULL , CONSTRAINT [PK_LogisticOrderMultiPostingPrincipalTariff] PRIMARY KEY CLUSTERED ( [LogisticOrderID] ASC , [TariffTypeID] ASC ) ON [PRIMARY] ) ON [PRIMARY]; ALTER TABLE [dbo].[LogisticOrderMultiPostingPrincipalTariff] ADD CONSTRAINT [DF_LogisticOrderMultiPostingPrincipalTariff_InsertUTCDate] DEFAULT (GETUTCDATE()) FOR [InsertUTCDate]; CREATE NONCLUSTERED INDEX [IX_ArticleID] ON [dbo].[LogisticOrderMultiPostingPrincipalTariff] ( [ArticleID] ASC ) ON [PRIMARY]; CREATE UNIQUE NONCLUSTERED INDEX [IX_Operation] ON [dbo].[LogisticOrderMultiPostingPrincipalTariff] ( [OperationID] ASC ) ON [PRIMARY]; CREATE NONCLUSTERED INDEX [IX_InsertUTCDate] ON [dbo].[LogisticOrderMultiPostingPrincipalTariff] ( [InsertUTCDate] ASC ) ON [PRIMARY]; END; GO

Таблица dbo.LogisticOrderMultiPostingPrincipalTariff содержит следующие поля:

• LogisticOrderID — идентификатор заказа;
• TariffTypeID — идентификатор типа тарификации;
• ArticleID — идентификатор отправления;
• OperationID — идентификатор операции;
• Amount — сумма тарификации;
• InsertUTCDate — дата и время создания записи в UTC (служебное поле).

Создаём хранимую процедуру для монопольного добавления записи в таблицу

Создадим хранимую процедуру dbo.AddLogisticOrderMultiPostingPrincipalTariff, которая будет в монопольном режиме добавлять в новую таблицу dbo.LogisticOrderMultiPostingPrincipalTariff запись с проверкой на существование записи с заданной парой (заказ, тип тарификации) и возвращать статус, показывающий, удалось добавить запись или нет (если нет, то запись уже была добавлена):

Определение хранимой процедуры dbo.AddLogisticOrderMultiPostingPrincipalTariff

SET ANSI_NULLS, QUOTED_IDENTIFIER ON; GO CREATE OR ALTER PROCEDURE dbo.AddLogisticOrderMultiPostingPrincipalTariff @LogisticOrderID int , @TariffTypeID int , @ArticleID int , @OperationID int , @IsResult bit = NULL OUT AS BEGIN SET NOCOUNT, XACT_ABORT ON; DECLARE @ID table ( ID int NULL ); SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; INSERT INTO dbo.LogisticOrderMultiPostingPrincipalTariff ( LogisticOrderID , TariffTypeID , ArticleID , OperationID , Amount ) OUTPUT Inserted.LogisticOrderID INTO @ID SELECT @LogisticOrderID , @TariffTypeID , @ArticleID , @OperationID , 0 WHERE (NOT EXISTS ( SELECT 1 FROM dbo.LogisticOrderMultiPostingPrincipalTariff WHERE (LogisticOrderID = @LogisticOrderID) AND (TariffTypeID = @TariffTypeID))); SET @IsResult = CASE WHEN (EXISTS ( SELECT 1 FROM @ID WHERE (ID > 0))) THEN 1 ELSE 0 END; END; GO

Хранимая процедура dbo.AddLogisticOrderMultiPostingPrincipalTariff принимает следующие параметры:

• LogisticOrderID — идентификатор заказа;
• TariffTypeID — идентификатор типа тарификации;
• ArticleID — идентификатор отправления;
• OperationID — идентификатор операции;
• IsResult — выходной параметр, возвращающий значение 1 (успех вставки) или 0 (неуспех — когда уже есть запись с таким же заказом и типом тарификации).

Обратите внимание, что при добавлении записи в таблицу dbo.LogisticOrderMultiPostingPrincipalTariff в хранимой процедуре dbo.AddLogisticOrderMultiPostingPrincipalTariff сумма тарификации Amount принимает значение 0, так как проставление этой суммы произойдёт позже через обновление.

Создаём хранимую процедуру для записи суммы тарифа

Создадим ещё одну хранимую процедуру. При каждой записи суммы тарифа сравниваем её с суммой из новой таблицы dbo.LogisticOrderMultiPostingPrincipalTariff и сначала обновляем в ней сумму Amount, если там она 0. В случае успешного обновления меняем сумму в операции. В противном случае (если в операции стоит иная сумма, то есть кто-то её уже обновил) — обнуляем её.

Создадим эту хранимую процедуру dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff:

Определение хранимой процедуры dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff

SET ANSI_NULLS, QUOTED_IDENTIFIER ON; GO CREATE OR ALTER PROCEDURE dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff @LogisticOrderID int , @TariffTypeID int , @ArticleID int , @OperationID int , @Amount money , @IsResult bit = NULL OUT AS BEGIN SET NOCOUNT, XACT_ABORT ON; SET @IsResult = NULL; DECLARE @NewAmount table ( Val money ); DECLARE @ArticleOldID int; SELECT @ArticleOldID = LOMPPT.ArticleID FROM dbo.LogisticOrderMultiPostingPrincipalTariff LOMPPT WHERE (LOMPPT.LogisticOrderID = @LogisticOrderID) AND (LOMPPT.TariffTypeID = @TariffTypeID); UPDATE LOMPPPPT SET Amount = @Amount , OperationID = @OperationID , ArticleID = @ArticleID OUTPUT inserted.Amount INTO @NewAmount FROM dbo.LogisticOrderMultiPostingPrincipalTariff LOMPPT WHERE (LOMPPT.LogisticOrderID = @LogisticOrderID) AND (LOMPPT.TariffTypeID = @TariffTypeID) AND ( (LOMPPT.Amount = 0) OR ( (LOMPPT.Amount <> @Amount) AND (LOMPPT.ArticleID = @ArticleID))); IF (COALESCE(( SELECT SUM(Val) FROM @NewAmount) , 0.0) = 0) BEGIN IF (@ArticleOldID <> @ArticleID) BEGIN SET @IsResult = 0; END; ELSE SET @IsResult = NULL; END; ELSE SET @IsResult = 1; END; GO

Хранимая процедура dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff принимает следующие параметры:

• LogisticOrderID — идентификатор заказа;
• TariffTypeID — идентификатор типа тарификации;
• ArticleID — идентификатор отправления;
• OperationID — идентификатор операции;
• Amount — сумма тарификации;
• IsResult — выходной параметр, возвращающий значение:
  • 1 — успех обновления;
  • 0 — неуспех, т е когда уже есть запись с нужной суммой
  • NULL — когда не нужно ничего менять, например при попытке записать сумму, которая уже указана, в то же отправление, на котором она указана.

  Эта хранимая процедура позволяет изменить сумму тарификации только для того отправления, у которого ранее была проставлена ненулевая сумма.

  Пример использования созданных хранимых процедур

  Давайте посмотрим, как пользоваться хранимыми процедурами, которые мы создали.

  У нас все отправления заказа создаются и переходят в статус «Сформировано» до того, как хотя бы одно из них куда-либо поедет. Напомним, что тарификация происходит при выдаче или возврате товара, когда сформированы все отправления заказа.

  При тарификации у нас на входе есть следующие параметры:

  • LogisticOrderID — идентификатор заказа;
  • TariffTypeID — идентификатор типа тарификации;
  • ArticleID — идентификатор отправления;
  • OperationID — идентификатор операции.

  На старте тарификации мы определяем, является ли заказ многоместным, то есть состоит ли он более чем из одного отправления. Если да, то вызывается хранимая процедура dbo.AddLogisticOrderMultiPostingPrincipalTariff для монопольного добавления записи о том, что мы стали тарифицировать заказ:

  EXEC dbo.AddLogisticOrderMultiPostingPrincipalTariff @LogisticOrderID = @LogisticOrderID , @TariffTypeID = @TariffTypeID , @ArticleID = @ArticleID , @OperationID = @OperationID , @IsResult = @IsResult OUT;

  По значению переменной @IsResult мы узнаём, получилось ли добавить запись. В нашем случае это нужно просто для информации и не используется для какой-либо логики в коде.

  Далее мы вычисляем сумму тарифа по определённому алгоритму и кладём её в переменную @Amount.

  На следующем этапе мы записываем ненулевую сумму тарификации @Amount в новую таблицу dbo.LogisticOrderMultiPostingPrincipalTariff через вызов хранимой процедуры dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff:

  EXEC dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff @LogisticOrderID = @LogisticOrderID , @TariffTypeID = @TariffTypeID , @ArticleID = @ArticleID , @OperationID = @OperationID , @Amount = @Amount , @IsResult = @IsResult OUT;

  Теперь смотрим на вернувшееся значение в переменной @IsResult:

  1. если @IsResult = 1, то сумму тарифа @Amount не меняем;
  2. если @IsResult = 0, то обнуляем сумму тарифа @Amount и после этого записываем получившуюся сумму тарификации @Amount в нужную операцию отправления заказа;
  3. если @IsResult IS NULL, то ничего не делаем.

  

  В случае же с одноместным заказом мы просто записываем сумму тарификации @Amount в соответствующую операцию отправления заказа.

  Ключ к уменьшению количества дублей — уровень изоляции транзакций

  Смотрите, что у нас получилось. Раньше стоимость доставки и возврата рассчитывалась по составу операций в рамках одного отправления. Теперь мы научились анализировать все отправления заказа для определения конечной операции с ним. Благодаря этому стало возможным автоматически рассчитывать корректные суммы для многоместных заказов.

  К этому решению мы пришли не сразу. Изначально мы искали способ, как избежать появления дублей в строках. А нужно было правильно применить уровень изоляции транзакций — так, чтобы блокировка на вставку и обновление данных была минимальной, то есть с выделением нового объекта для блокировки и синхронизации, которым стала новая таблица, описанная выше.

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

  

  Приведённое решение можно использовать на разных СУБД в задачах, где происходят асинхронные вставки и обновления данных разными процессами и из-за этого возможно дублирование данных.

  Такие задачи распространены во многих сферах:

  • логистика,
  • транспортная безопасность,
  • складской учёт,
  • финансы,
  • бухгалтерский учёт,
  • строительство,
  • аудит.

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

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

  Ссылки по теме

  1. Транзакции MS SQL Server: как вызывать транзакции, откатывать и фиксировать их, какие существуют уровни изоляции транзакций и различия между ними.
  2. Предложение OUTPUT: как получить то, что вставили, обновили (заодно смотрим, что было до обновления) или удалили при различных командах модификации данных.
  3. MS SQL Server: общая документация по СУБД, которую я выбрал для реализации описанного метода предотвращения дублирования данных.

  Только зарегистрированные пользователи могут участвовать в опросе. Войдите, пожалуйста.

  А какие способы синхронизации асинхронных процессов записи используете вы при вставке или обновлении данных в разных строках таблицы?

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

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

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

  name John Smith John Smith Smith Tom

  Для простоты я не включаю сюда другие столбцы, предполагая, что данные в них однозначно определяются значением в столбце 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 SELECT * FROM T; 

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

  name John Smith Tom

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

  А можно ли обойтись без создания новой таблицы? Можно. Например, с помощью такого алгоритма:
  — добавить новый столбец типа счетчик (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); GO 

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

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

  1. Нумерация строк

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

   SELECT *, ROW_NUMBER() OVER(ORDER BY name) num FROM T 

  В результате получим

  name num John 1 John 2 Smith 3 Smith 4 Smith 5 Tom 6

  2. Ранжирование строк внутри групп дубликатов

  К сожалению, запрещено (MS SQL Server) использовать оконные функции внутри оконных функций. Т.е. мы не можем написать так:

   SELECT name, RANK() OVER (PARTITION BY name ORDER BY ROW_NUMBER() OVER(ORDER BY name)) rnk FROM T; 

  а потому используем подзапрос:

   SELECT name, RANK() OVER (PARTITION BY name ORDER BY num) rnk FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name) num FROM T ) X; 

  Ниже представлен результат этого запроса.

  name rnk John 1 John 2 Smith 1 Smith 2 Smith 3 Tom 1

  3. Удаление дубликатов из виртуальной таблицы

  Недопустимо удалять записи из запроса, т.е. мы не можем воспользоваться таким вариантом:

   DELETE FROM (SELECT name, RANK() OVER (PARTITION BY name ORDER BY num) rnk FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name) num FROM T ) X WHERE rnk > 1; 

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

   CREATE VIEW Tview AS SELECT name, RANK() OVER (PARTITION BY name ORDER BY num) rnk FROM(SELECT *, ROW_NUMBER() OVER(ORDER BY name) num FROM T ) X GO DELETE FROM Tview WHERE rnk > 1; 

  "Опять что-то создавать", - скажете вы. Не обязательно, и, чтобы доказать это, нам помогут общие табличные выражения (CTE), которые можно назвать виртуальными представлениями. CTE, хотя и не являются сохраняемыми в базе данных объектами, могут использоваться с операторами обновления. В результате все сводится к одному запросу:

   WITH CTE AS (SELECT name, RANK() OVER (PARTITION BY name ORDER BY num) rnk FROM(SELECT *, ROW_NUMBER() OVER(ORDER BY name) num FROM T ) X ) DELETE FROM CTE WHERE rnk > 1; GO 

  Не забудьте только создать первичный ключ. 🙂

  Попал по внешней ссылке на эту статью и решил себе возразить. 🙂

  Вот эта фраза: "Мы не можем сразу ранжировать строки просто потому, что их не по чем ранжировать."

  Разумеется, это правильно, но мы можем отказаться от ранжирования (в ущерб обучению :-)), выполнив "псевдоранжирование". Дело в том, что есть возможность выполнить независимую нумерацию для каждой группы, если в предложении OVER для функции ROW_NUMBER использовать конструкцию PARTITION BY. Итак, можно вообще обойтись без функции RANK, если выполнить разбиение по name

   SELECT name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) FROM T; 

  Это упростит все последующие запросы, в частности, последнее решение задачи удаления дубликатов можно переписать в виде:

   WITH CTE AS ( SELECT name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) rnk FROM T ) DELETE FROM CTE WHERE rnk > 1; 

  Похожие публикации:

  1. Как включить гибернацию в windows 10
  2. Как выставить зажигание на уаз 402 двигатель карбюратор по меткам
  3. Как запустить бота через node js
  4. Как узнать геопозицию абонента через whatsapp