Удаление повторяющихся строк из SQL Server с помощью сценария
В этой статье приведен сценарий, который можно использовать для удаления повторяющихся строк из таблицы в Microsoft SQL Server.
Оригинальная версия продукта: SQL Server
Оригинальный номер базы знаний: 70956
Сводка
Существует два распространенных метода, которые можно использовать для удаления повторяющихся записей из таблицы SQL Server. В демонстрационных целях начните с создания образца таблицы и данных:
create table original_table (key_value int ) insert into original_table values (1) insert into original_table values (1) insert into original_table values (1) insert into original_table values (2) insert into original_table values (2) insert into original_table values (2) insert into original_table values (2) Затем попробуйте следующие методы, чтобы удалить повторяющиеся строки из таблицы.
Способ 1
Запустите следующий сценарий:
SELECT DISTINCT * INTO duplicate_table FROM original_table GROUP BY key_value HAVING COUNT(key_value) > 1 DELETE original_table WHERE key_value IN (SELECT key_value FROM duplicate_table) INSERT original_table SELECT * FROM duplicate_table DROP TABLE duplicate_table Этот сценарий выполняет следующие действия в указанном порядке:
- Перемещает один экземпляр любой повторяющейся строки в исходной таблице в дублирующую таблицу.
- Удаляет все строки из исходной таблицы, которые также находятся в дублирующей таблице.
- Перемещает строки в дублирующей таблице обратно в исходную таблицу.
- Удаляет дублирующую таблицу.
Этот метод прост в использовании. Однако для временного создания дублирующей таблицы в базе данных требуется достаточно места. Этот метод также влечет за собой дополнительные затраты, так как данные перемещаются.
Кроме того, если таблица содержит столбец IDENTITY, при восстановлении данных в исходной таблице необходимо использовать SET IDENTITY_INSERT ON.
Способ 2
Функция ROW_NUMBER, добавленная в Microsoft SQL Server 2005, значительно упрощает эту операцию:
DELETE T FROM ( SELECT * , DupRank = ROW_NUMBER() OVER ( PARTITION BY key_value ORDER BY (SELECT NULL) ) FROM original_table ) AS T WHERE DupRank > 1 Этот сценарий выполняет следующие действия в указанном порядке:
- Использует ROW_NUMBER функцию для разделения данных на основе key_value , в роли которого может выступать один или несколько столбцов, разделенных запятыми.
- Удаляет все записи, которые получили значение DupRank , превышающее 1. Это указывает на то, что записи являются дубликатами.
Из-за выражения (SELECT NULL) сценарий не сортирует разделенные данные на основе каких-либо условий. Если ваша логика удаления дубликатов требует выбора того, какие записи удалить, а какие оставить, основываясь на порядке сортировки других столбцов, можно использовать для этого выражение ORDER BY.
Дополнительная информация
Метод 2 является простым и эффективным по следующим причинам:
- Для этого не требуется временно копировать повторяющиеся записи в другую таблицу.
- При этом не требуется объединения исходной таблицы с самой собой (например, с помощью подзапроса, который возвращает все повторяющиеся записи с помощью комбинации GROUP BY и HAVING).
- Для достижения оптимальной производительности в таблице должен быть соответствующий индекс, который использует key_value в качестве ключа индекса и содержит любые столбцы сортировки, которые могли использоваться в выражении ORDER BY.
Однако этот метод не работает в устаревших версиях SQL Server, которые не поддерживают функцию ROW_NUMBER. В этом случае следует использовать метод 1 или аналогичный метод.
Обратная связь
Были ли сведения на этой странице полезными?
Исключение дубликатов
Иногда в таблицах встречаются дубликаты, как например имена сотрудников. Для исключения повторяющихся строк, нужно после ключевого слова SELECT написать DISTINCT .
Чтобы получить список уникальных имен сотрудников, необходимо выполнить запрос:
SELECT DISTINCT first_name FROM employee | first_name |
|---|
| Анна |
| Лука |
| Ольга |
| . |
Как вывести дубликаты в sql
WordPress 6 с Нуля до Гуру
Этот курс научит Вас созданию самых разных сайтов на самой популярной в мире CMS — WordPress. Вы увидите установку и настройку локального сервера, разбор каждой настройки, каждой кнопки и каждого пункта меню в панели WordPress.
Также Вы получите и всю практику, поскольку прямо в курсе с нуля создаётся полноценный Интернет-магазин, который затем публикуется в Интернете. И всё это прямо на Ваших глазах.
Помимо уроков к курсу идут упражнения для закрепления материала.
И, наконец, к курсу идёт ценнейший Бонус по тому, как используя ChatGPT и создавая контент для сайта, можно выйти на пассивный доход. Вы наглядно увидите, как зарегистрироваться в ChatGPT (в том числе, и если Вы из России), как правильно выбрать тему для сайта, как правильно генерировать статьи для него(чтобы они индексировались поисковыми системами) и как правильно монетизировать трафик на сайте.
Уроки и статьи
Подпишитесь на мой канал на YouTube, где я регулярно публикую новые видео.
Подписаться
Подписавшись по E-mail, Вы будете получать уведомления о новых статьях.
Подписаться
Добавляйтесь ко мне в друзья ВКонтакте! Отзывы о сайте и обо мне оставляйте в моей группе.
Мой аккаунт Моя группа
Какая тема Вас интересует больше?
Бесплатный курс
Основы Unreal Engine 5
Пройдя курс:
— Вы получите необходимую базу по Unreal Engine 5
— Вы познакомитесь с множеством инструментов в движке
— Вы научитесь создавать несложные игры
Общая продолжительность курса 4 часа, плюс множество упражнений и поддержка!
Чтобы получить Видеокурс,
заполните форму
Бесплатный онлайн-семинар
Как создать профессиональный Интернет-магазин
После семинара:
— Вы будете знать, как создать Интернет-магазин.
— Вы получите бесплатный подарок с подробным описанием каждого шага.
— Вы сможете уже приступить к созданию Интернет-магазина.
Удаление или поиск дубликатов (повторяющихся) записей в таблице
Начнем с того, что важно определить, отличаются записи хоть одним полем или нет. Оператор DELETE и вообще SQL не позволяет из двух одинаковых записей удалить только одну – либо все дубликаты, либо ни одного.
Однако, используя специфический для IB номер записи, это можно сделать. Например:
DELETE FROM XXX T1 WHERE EXISTS
(SELECT * FROM XXX T2 WHERE
(T2.column1 = T1.column1 or (T2.column1 is null and T2.column1 is null)) AND
(T2.column2 = T1.column2 or (T2.column2 is null and T2.column2 is null)) AND
(. ) AND
( T2.RDB$DB_KEY > T1.RDB$DB_KEY ))
В этом случае используется RDB$DB_KEY – физический номер записи IB. Можно оставить как запись с самым большим DB_KEY, так и с самым меньшим (> или < в последнем условии WHERE).
Для поиска имеющих какой-либо одинаковый столбец записей обычно используется запрос, похожий на следующий:
SELECT * FROM TABLE T1
WHERE (SELECT COUNT(*)
FROM TABLE T2
WHERE T1.FIELD = T2.FIELD ) > 1
Однако этот запрос не совсем эффективен. Вместо него выгоднее использовать процедуру, которая будет выполняться намного быстрее:
(Ann Harrison)
for select field
from table
group by field
having count (field) > 1
into :fld
do
begin
for select field
from table
where field = :fld
into :fld1
do
begin
suspend;
end
end
Но хранимая процедура не всегда удобна. Также можно использовать уникальный идентификатор записи RDB$DB_KEY:
(Josef Marie M. Alba)
SELECT * FROM TABLE T1
WHERE EXISTS
(SELECT FIELD FROM TABLE T2
WHERE T1.FIELD = T2.FIELD AND
T1.RDB$DB_KEY != T2.RDB$DB_KEY )
Copyright iBase.ru © 2002-2024