Как исключить дубликаты в sql
Удаление повторяющихся строк из таблицы в SQL Server
Аннотация
Таблицы Microsoft SQL Server никогда не должны содержать повторяющихся строк и неуникальных первичных ключей. Для краткости в этой статье мы будем иногда называть первичные ключи просто ключами, но подразумеваться всегда будут именно первичные ключи. Повторяющиеся ключи нарушают целостность сущностей, поэтому должны быть запрещены в реляционной системе. SQL Server содержит ряд механизмов, обеспечивающих целостность сущностей, включая индексы, ограничения UNIQUE, ограничения PRIMARY KEY и триггеры.
Несмотря на это, в необычных обстоятельствах дублирование первичных ключей все же возможно, и от него необходимо избавиться. Дублирование первичных ключей возможно, например, если повторяющиеся первичные ключи имеются в нереляционных данных вне SQL Server и импорт данных осуществляется без обеспечения уникальности первичных ключей. Это также возможно, если при проектировании базы данных была допущена ошибка, например если не обеспечивается целостность сущностей для каждой таблицы.
Повторяющиеся ключи часто обнаруживаются при попытке создать уникальный индекс. Если они имеются, эта операция прерывается. При этом выводится следующее сообщение.
Msg 1505, Level 16, State 1 Create unique index aborted on duplicate key.
Если используется SQL Server 2000 или SQL Server 2005, может быть выведено следующее сообщение об ошибке.
Сообщение 1505, уровень 16, состояние 1 Операция CREATE UNIQUE INDEX прервана, так как обнаружен повторяющийся ключ для имени объекта «%1!s!» и имени индекса «%2!s!». Повторяющееся значение ключа: %ls.
В данной статье рассматривается процедура обнаружения и удаления повторяющихся первичных ключей из таблицы. Однако при обнаружении повторяющихся ключей следует также тщательно изучить процедуру их появления, чтобы предотвратить возникновение этой проблемы в будущем.
Дополнительная информация
Проблема дублирования ключей будет рассмотрена на примере следующей таблицы с повторяющимся первичным ключом. В данной таблице первичным ключом являются два столбца (col1, col2). Создать уникальный индекс или ограничение PRIMARY KEY в данном случае невозможно, поскольку две строки имеют повторяющиеся первичные ключи. Можно воспользоваться процедурой, описанной ниже, чтобы обнаружить и удалить дубликаты.
Первым делом следует определить, какие строки имеют повторяющиеся значения первичного ключа.
Этот код возвращает одну строку для каждого набора повторяющихся значений первичного ключа в таблице. Последний столбец в полученном результате представляет число дубликатов определенного значения первичного ключа.
Если повторяющихся значений первичного ключа мало, лучше всего удалить их по отдельности вручную. например:
Значение rowcount должно быть на единицу меньше, чем число дубликатов определенного значения ключа. В данном примере дубликатов два, поэтому переменной rowcount присваивается значение 1. Значения col1 и col2 берутся из результата выполнения запроса GROUP BY, приведенного выше. Если запрос GROUP BY возвращает несколько строк, запрос «set rowcount» нужно будет выполнить по одному разу для каждой из этих строк. Переменной rowcount при каждом запуске нужно должно присваиваться значение на единицу меньше числа дубликатов определенного значения первичного ключа.
Перед удалением строки следует убедиться, что продублирована вся строка. Хотя это и маловероятно, значения первичного ключа могут быть продублированы, в то время как вся строка — нет. В качестве примера можно привести таблицу с номером социального страхования в качестве первичного ключа и двумя разными строками (представляющими людей) с уникальными атрибутами и одинаковым номером. В этом случае проблема, приведшая к дублированию ключа, могла также стать причиной записи в строку правильных уникальных данных. Перед удалением эти данные следует скопировать и сохранить для анализа и возможной сверки.
Если таблица содержит большое число разных наборов повторяющихся значений первичного ключа, на их удаление вручную может потребоваться слишком много времени. В этом случае можно воспользоваться нижеуказанной процедурой.
Сначала выполните приведенный выше запрос GROUP BY, чтобы узнать количество наборов повторяющихся значений первичного ключа и число дубликатов в каждом наборе.
Выделите повторяющиеся значения ключа в отдельную таблицу, например:
Выделите повторяющиеся строки в отдельную таблицу, удаляя при этом дубликаты, например:
Теперь таблица holddups должна содержать уникальные первичные ключи, однако это условие не будет выполнено, если таблица t1 включает повторяющиеся первичные ключи и уникальные строки (как в приведенном выше примере с номерами социального обеспечения). Убедитесь в том, что каждый ключ в таблице holddups уникален и что у вас нет повторяющихся ключей при наличии уникальных строк. Если это так, подумайте, какие из строк нужно сохранить для определенного повторяющегося значения ключа. Например, запрос
должен вернуть для каждой строки значение счетчика, равное 1. Если это так, перейдите к действию 5. Если нет, повторяющиеся ключи имеются при наличии уникальных строк, и тогда нужно решить, какие строки следует сохранить. Как правило, при этом или строка удаляется, или для нее создается уникальное значение ключа. Сделайте или первое, или второе для каждого такого повторяющегося ключа в таблице holddups.
Удалите повторяющиеся строки из исходной таблицы, например:
Поместите уникальные строки обратно в исходную таблицу, например:
Вывод без дубликатов в SQL
Добрый день! Есть у меня 2 таблицы.
и вторая таблица client_contacts
3 ответа 3
так как вам нету разницы какую из номеров брать. А так есть еще один метод. Склеить эти строки.
Если я правильно понял вопрос то речь идет об объединение строк и вывода номеров телефонов через запятую. Для mysql группировку можно выполниться с помощью GROUP_CONCATE и GROUP BY.
Для SQL Server подобного простого решения похоже нет. Как эмулировать group_concat на SQL Server хорошо описано здесь и здесь. Я не специалист по SQL Server и воздержусь от приведения примера конкретно для SQL Server.
Всё ещё ищете ответ? Посмотрите другие вопросы с метками sql sql-server или задайте свой вопрос.
Похожие
Подписаться на ленту
Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.
дизайн сайта / логотип © 2021 Stack Exchange Inc; материалы пользователей предоставляются на условиях лицензии cc by-sa. rev 2021.12.22.41046
Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.
Как исключить дубликаты в sql
Программирование на C++ с Нуля до Гуру
Данный курс научит Вас программировать на языке C++, который, несмотря на свой почтенный возраст, необычайно сильно востребован. Курс состоит из 6 разделов, посмотрев которые и выполнив все упражнения, Вы с нуля освоите этот язык и сможете создавать самые разные проекты любой сложности на C++.
Для закрепления материала из уроков к ним идёт множество упражнений.
Дополнительно к курсу идёт вспомогательная система, которая не даст Вам забросить начатое на полпути.
Также вместе с курсов Вы получаете Бонус «Программирование на C++ в Unreal Engine», в котором Вы научитесь создавать игры на C++ с использованием этого движка.
Подпишитесь на мой канал на YouTube, где я регулярно публикую новые видео.
Подписавшись по E-mail, Вы будете получать уведомления о новых статьях.
Добавляйтесь ко мне в друзья ВКонтакте! Отзывы о сайте и обо мне оставляйте в моей группе.
Зачем Вы изучаете программирование/создание сайтов?
Программирование на C++ для начинающих
Бесплатный курс по C++ даст Вам отличный старт для начала программирования на этом языке. В курсе Вас ждёт множество уроков (больше 4-х часов видео), исходников и упражнений.
Чтобы получить Видеокурс,
заполните форму
Как создать профессиональный Интернет-магазин
— Вы будете знать, как создать Интернет-магазин.
— Вы получите бесплатный подарок с подробным описанием каждого шага.
— Вы сможете уже приступить к созданию Интернет-магазина.
Удалить повторяющиеся строки в MySQL
У меня есть таблица со следующими полями:
После выполнения этого запроса я могу удалить дубликаты с помощью сценария на стороне сервера.
Но я хочу знать, можно ли это сделать только с помощью SQL-запроса.
25 ответов
Если вы не хотите изменять свойства столбца, вы можете использовать запрос ниже.
Поскольку у вас есть столбец с уникальными идентификаторами (например, столбцы auto_increment ), вы можете использовать его для удаления дубликатов:
В MySQL вы можете еще больше упростить его с помощью NULL-безопасный оператор равенства (также известный как «оператор космического корабля») :
MySQL имеет ограничения на обращение к таблице, из которой вы удаляете. Вы можете обойти это с помощью временной таблицы, например:
Если инструкция IGNORE не будет работать, как в моем случае, вы можете использовать следующую инструкцию:
В самом MySQL также есть некоторые особенности, такие как невозможность ссылаться на ту же таблицу по причине FROM при выполнении ОБНОВЛЕНИЯ таблицы (это вызовет ошибку MySQL # 1093). Это ограничение можно преодолеть, используя внутренний запрос с временной таблицей (как предложено в некоторых подходах выше). Но этот внутренний запрос не будет работать особенно хорошо при работе с большими источниками данных.
Однако существует лучший подход для удаления дубликатов, который одновременно эффективен и надежен и может быть легко адаптирован к различным потребностям.
Общая идея состоит в том, чтобы создать новую временную таблицу, обычно добавляя уникальное ограничение, чтобы избежать дальнейшего дублирования, и ВСТАВИТЬ данные из вашей предыдущей таблицы в новую, заботясь о дубликатах. Этот подход основан на простых запросах MySQL INSERT, создает новое ограничение, чтобы избежать дальнейших дубликатов, и пропускает необходимость использования внутреннего запроса для поиска дубликатов и временной таблицы, которая должна храниться в памяти (таким образом, подходящая для больших источников данных).
Вот как этого можно достичь. Учитывая, что у нас есть таблица employee со следующими столбцами:
Чтобы удалить строки с повторяющимся столбцом ssn и оставить только первую найденную запись, можно выполнить следующий процесс:
Техническое объяснение
⇒ При таком подходе 1,6 млн регистров были преобразованы в 6 КБ менее чем за 200 с.
Chetan, следуя этому процессу, вы можете быстро и легко удалить все свои дубликаты и создать ограничение UNIQUE, запустив:
Конечно, этот процесс можно дополнительно изменить, чтобы адаптировать его к различным потребностям при удалении дубликатов. Ниже приведены некоторые примеры.
✔ Вариант сохранения последней записи вместо первой
Иногда нам нужно сохранить последнюю дублированную запись вместо первой.
✔ Вариант выполнения некоторых задач с дубликатами, например, подсчет найденных дубликатов
Иногда нам нужно выполнить некоторую дополнительную обработку найденных дублированных записей (например, подсчитать количество дубликатов).
✔ Вариант для регенерации идентификатора автоинкрементного поля
Иногда мы используем автоинкрементное поле и, чтобы индекс оставался как можно более компактным, мы можем воспользоваться преимуществом удаления дубликатов для регенерации автоинкрементного поля в новой временной таблице.
✔ Дальнейшие вариации
Также возможны многие дальнейшие модификации в зависимости от желаемого поведения. В качестве примера следующие запросы будут использовать вторую временную таблицу, помимо 1) сохранения последней записи вместо первой; и 2) увеличить счетчик найденных дубликатов; также 3) регенерировать идентификатор автоинкрементного поля, сохраняя при этом порядок ввода, как это было в прежних данных.
Удаление повторяющихся строк из таблицы в Oracle
Я тестирую что-то в Oracle и заполняю таблицу некоторыми образцами данных, но в процессе я случайно загрузил дубликаты записей, поэтому теперь я не могу создать первичный ключ, используя некоторые столбцы.
Как удалить все повторяющиеся строки и оставить только одну из них?
21 ответов
использовать rowid псевдостолбцом.
(исправлена отсутствующая скобка)
где столбец1, столбец2 и т. д. это ключ, который вы хотите использовать.
создать таблицу t2 как выбрать distinct * from t1;
Решение 1)
решение 2)
решение 3)
для выбора дубликатов только формат запроса может быть:
таким образом, правильный запрос в соответствии с другим предложением:
Oracle Certified Associate (2008)
решение 4)
1. решение
2. натра
3.решение
4. решение
5. решение
и вы также можете удалить дубликаты записей другим способом
вы должны сделать небольшой блок pl / sql, используя курсор для цикла и удалить строки, которые вы не хотите сохранять. Например:
самый быстрый способ для действительно больших таблиц
создать таблицу исключений со структурой ниже: exceptions_table
попробуйте создать уникальное ограничение или первичный ключ, который будет нарушен дубликатами. Вы получите сообщение об ошибке, потому что у вас есть дубликаты. Таблица исключений будет содержать rowids для повторяющихся строк.
Присоединяйтесь к таблице с exceptions_table по rowid и дублированные файлы удалить
если количество строк для удаления велико, то создайте новую таблицу (со всеми грантами и индексами) антисоединение с exceptions_table по rowid и переименуйте исходную таблицу в таблицу original_dups и переименуйте new_table_with_no_dups в исходную таблицу
вы увидите, что дубликаты записей были удалены.
Надеюсь, это решит ваш запрос. Спасибо 🙂
Я не видел ответов, которые используют общие табличные выражения и оконные функции. С этим мне легче всего работать.
что-то в примечание:
1)мы проверяем только дублирование полей в предложении partition.
2) Если у вас есть причина выбрать один дубликат над другими, вы можете использовать предложение order by, чтобы эта строка имела row_number () = 1
3) Вы можете изменить номер дубликата, сохраненный изменение предложения final where на «Where RN > N» с N >= 1 (я думал, что N = 0 удалит все строки, которые имеют дубликаты, но он просто удалит все строки).
4) добавлено поле Sum partition запрос CTE, который будет помечать каждую строку числовыми строками в группе. Поэтому для выбора строк с дубликатами, включая первый элемент, используйте «где cnt > 1».