Space Base Space Base
+7 928 008-80-89
ru
  • en
  • es
  • Главная
  • Услуги
  • Портфолио
  • Библиотека
  • Контакты
  • Главная
  • Услуги
  • Портфолио
  • Библиотека
  • Контакты
  1. Библиотека
  2. SQL
  3. Удаление повторяющихся строк в таблице запросом SQL
logo

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

09.03.2015

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

В этой статье рассмотрим задачу удаления дублирующих строк в таблице БД. Сразу же отмечу, что речь идет о необходимости удалить именно повторяющиеся строки. Например, записи в таблице заказов с полями "код заказа", "код товара", "код покупателя", "дата заказа" могут различаться только кодом заказа, так как все же один покупатель в один день может заказать один и тот же товар несколько раз. А главный показатель здесь, что все правильно – наличие ключевого поля.

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

Пример явно избыточной таблицы:

id (код записи) country_id (код страны) city_name (код города)
1 1 Москва
2 1 Хабаровск
3 1 Самара
4 1 Кисловодск
5 1 Хабаровск
6 1 Самара
7 1 Кисловодск
8 1 Кисловодск

Теперь рассмотрим, как можно решить эту проблему. Здесь можно применить несколько методов.

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

2. Другое решение – создать запрос на выборку с группировкой данных, так чтобы получить только уникальные строки:

SELECT country_id, city_name
    FROM mytable
    GROUP BY country_id, city_name

Получаем следующую выборку:

country_id city_name
1 Кисловодск
1 Москва
1 Самара
1 Хабаровск

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

3. В указанных решениях применяется дополнительный программный код или дополнительные таблицы. Однако, было бы удобней сделать все, используя только запросы SQL без дополнительных таблиц. И вот пример такого решения:

DELETE a.* FROM mytable a,
    (SELECT
       b.country_id, b.city_name, MIN(b.id) mid
       FROM mytable b
       GROUP BY b.country_id, b.city_name
     ) c
WHERE
     a.country_id = c.country_id
     AND a.city_name = c.city_name
     AND a.id > c.mid

После выполнения такого запроса в таблице останутся только уникальные записи:

id  country_id  city_name 
1 1 Москва
2 1 Хабаровск
3 1 Самара
4 1 Кисловодск

Теперь разберемся подробнее, как все это работает. При запросе на удаление, необходимо задать условие, которое укажет какие данные нужно удалить, а какие оставить. Нам необходимо удалить все не уникальные записи. Т.е. если существует несколько одинаковых записей (одинаковые они, если у них равны значения country_id и city_name), то нужно взять одну из строк, запомнить ее код и удалить все записи с такими же значениями country_id и city_name, но другим кодом (id).

Строка SQL запроса:

DELETE a.* FROM mytable a,

указывает, что удаление будет производиться из таблицы mytable.

Затем запрос на выборку формирует вспомогательную таблицу, где мы группируем записи так, чтобы все записи были уникальными:

 (SELECT
      b.country_id, b.city_name, MIN(b.id) mid
      FROM mytable b
      GROUP BY b.country_id, b.city_name
  ) c

MIN(b.id) mid – формирует столбец mid (сокращение min id), в который вносятся минимальное значение id, в каждой подгруппе.

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

country_id  city_name  mid
1 Кисловодск 4
2 Москва  1
3 Самара 3
4 Хабаровск 2

Теперь мы имеем две таблицы. Одну общую, содержащую все записи. Из нее будут удаляться лишние строки. Вторая содержит информацию о строках, которые нужно сохранить.

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

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

ALTER TABLE ` mytable` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT , ADD PRIMARY KEY ( `id` )

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

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

Другие материалы:

Запросы SQL добавления данных (INSERT)


Копирование в таблицу данных из другой таблицы запросом SQL


Язык структурированных запросов SQL


Написать комментарий

Комментарии

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


коммент.

avatar

Антон Сенников

- 04 октября 2022, 13:55 ↓
Если нужно удалить именно дублирующие записи, мне кажется тоже вполне возможным. Но нужно понимать по каким правилам они должны быть удалены и какие дополнительные условия (количество записей, например). Если ситуация например:
1. много записей — руками удалять нереально.
2. есть дублирующие записи и неважно, какие именно из повторов удалять.
3. других условий нет.

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

Фаиль

- 04 октября 2022, 13:37 ↓
К сожалению могут Антон. И мне нужно удалить дубли с одинаковыми ID. Хотел через функцию Row_number. Сначала хотел вывести все поля, в которых row_number больше одного. Но в where нельзя использовать название. Как ввести в where условие на оконную функцию?
avatar

Антон Сенников

- 04 октября 2022, 12:33 ↓
ID не могут быть одинаковыми. Иначе это уже не ID (идентификатор — то, что однозначно определяет уникальную запись).
В случае, если у вас некорректная БД, то стоит ввести отдельное поле где назначить уникальные идентификаторы, а далее уже что-то продумывать исходя из новых ключей.
avatar

Фаиль

- 04 октября 2022, 11:58 ↓
А как удалить дубли, если одинаковые ID?
avatar

Никита

- 19 апреля 2021, 04:58 ↓
Огромное спасибо, перелопачено не один час из 50 предложенных решений без костылей не работает 51, но ваше наконец сработало
avatar

Антон Сенников

- 26 октября 2020, 16:52 ↓
Илья, спасибо за ваше замечание. Не проверял на больших объемах данный запрос. При случае воспользуюсь вашей рекомендацией.
avatar

Илья

- 26 октября 2020, 13:21 ↓
Большое спасибо! Очень помогло.
Кстати, если в подзапрос:
(SELECT
b.country_id, b.city_name, MIN(b.id) mid
FROM mytable b
GROUP BY b.country_id, b.city_name
) c
добавить условие HAVING COUNT(*) > 1, то поиск по большим таблицам данных будет происходить значительно быстрее.
avatar

евгений

- 20 июня 2020, 21:12 ↓
Круто! Спасибо!

Разработка сайтов

Корпоративный сайт
Интернет-магазин
Лендинг - одностраничный сайт
Сайт-визитка
Сайт-портфолио

Проектирование

Прототип, UX-дизайн

Дизайн

UI-дизайн
Логотип

+54 911 2801-4950

info@space-base.net
+7 928 008-80-89

Web-сайты для успешного бизнеса

Web-сайты для успешного бизнеса

Главная Услуги Портфолио События Библиотека Контакты
+7 928 008-80-89 Меню
Политика в отношении обработки персональных данных © Copyright 2014 - | Space-Base

Лучшее время начать свой проект - Сейчас!

Выбраны опции:

Отправить сообщение на:

Telegram WhatsApp

Отправляя сообщение, вы даете свое согласие на
обработку песональных данных