Как найти неуникальные записи в sql таблице

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

Собственно есть необходимость поиска и вывода таких записей, для которых существует по крайней мере еще одна запись, у которой содержимое проверяемой строки точно такое же. Для показа таких записей, необходимо в PhpMyAdmin или в SQL Server(смотря где и над чем работаете) выполнить запрос следующего вида.

[sql]select * from wp_posts WHERE post_type="page" group by post_title having count(post_title) > 1 ORDER BY `post_title` ASC[/sql]

Те кто использует CMS WordPress должны легко понять что здесь что:

• wp_posts — название таблицы, в которой производится поиск
• post_type=»page» — необязательный параметр, который помогает убрать из выдачи ненужные записи.
• post_title — название строки, для которого мы ищем записи с одинаковым содержимым.

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

И собственно вопрос с удалением будет решаться либо с использованием графического интерфейса(выбрать все и нажать Удалить), либо заменой Select на Delete.

Таким образом можно найти записи с неуникальными строками в пределах заданной таблицы базы данных SQL.

SQL: найти неуникальные записи в одном столбце, которые также не уникальны в другом столбце

У меня есть таблица мест и их регионов, но иногда в одном регионе есть несколько мест с одинаковым названием. Примеры из реальной жизни: в Висконсине есть пять Спрингфилдов. В Новой Шотландии, где я живу, есть три песчаных бухты. Мне нужно найти записи такого рода и устранить их неоднозначность (например, добавив их округ или эквивалент) или просто удалить ненужные / перекрывающиеся. Это займет некоторую работу, но сначала я хотел бы посмотреть, сколько работы.

Этот запрос, основанный на ответе на другой вопрос, дает мне все места с одинаковым именем:

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

(FWIW, я использую MariaDB, который в основном совместим с MySQL.)

4 ответа

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

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

TablePlus

How to count unique rows in MySQL with COUNT DISTINCT?

Overview

In MySQL, COUNT() function returns the number of rows in a table.

COUNT(DISTINCT expression) returns the number of rows that contain non NULL values as the result of the specified expression.

Syntax

To to use COUNT(DISTINCT) in a SELECT statement:

Example

We have table sales :

item	customer_name
book	Alex
pen	Bob
book	Alex
book	Jim
shoes	Jim
pen	Bob
backpack	Bob
book	Bob
wallet	Alex
wallet	Alex
book	Bob
backpack	Jim

Since a customer might have multiple purchases, the customer_name field has duplicate values. So to count the number of unique customers:

The result is 3.

A customer might purchased one item multiple times, to see how many unique items each customer already purchased, we can use GROUP BY :

customer_name	unique_item
Alex	2
Bob	3
Jim	3

We can also include WHERE to count the number of unique customers that bought the item book :

The result is 3.

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.

SQL — урок 4. Выборка данных из базы

Выбор данных выполняется командой SELECT.
Ниже приведен примерный список используемых ею конструкций и ключевых слов, полный же список зависит от реализации СУБД:

• AS — определяет временный синоним источника данных или столбца;
• FROM — указывает источники данных как таблицы, представления, другие выборки. По необходимости здесь можно указать соединение источников — каким образом запись одного источника сопоставляется с записью другого;
• WHERE — позволяет указать условия по которым нужно производить отбор данных. Если хоть одно из перечисленных условий не выполняется, запись не попадает в выборку. Здесь также можно указать соединение источников;
• ORDER BY — позволяет отсортировать выборку по указанным полям;
• ASC, DESC — задают направление сортировки;
• GROUP BY — позволяет разбить выборку на группы по указанному полю. Все записи, имеющие одно и то же значение в указанном поле, будут принадлежать одной группе;
• HAVING — позволяет задать условие включения группы в выборку. Набор возможных условий как у WHERE плюс возможность использования агрегатных функций;
• FOR UPDATE — позволяет заблокировать выбранные данные для изменения;
• DISTINCT — позволяет включить в выборку только уникальные записи. Конечно это замедляет запрос, но бывает необходимо при использовании агрегатных функций.

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

выборка констант

Для выбора констант может указываться любой источник. Однако, если мы хотим просто
подсчитать значение какого-то выражения, то указывать источник имеющий сотню тысяч записей затратно по ресурсам. Поэтому многие СУБД позволяют выбрать константы без указания источника. Oracle не поддерживает синтаксис SELECT без FROM, а для выбора констант используется специальная системная таблица dual.

выборка по столбцам таблиц

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

синонимы (алиасы)

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

уникальные записи

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

Конструкция ORDER BY позволяет последовательно отсортировать сразу по нескольким столбцам. Столбцы, по которым происходит сортировка, желательно проиндексировать.

выборка по условию

Конструкция WHERE позволяет ограничить множество выбираемых записей. Ниже приведено несколько примеров.

выборка по группам

И напоследок пару примеров группировки данных.

Соединения (Join)

Этот раздел написан на основе материалов сайта Javenue.

Ключевое слово join в SQL используется при построении select выражений. Инструкция Join позволяет объединить колонки из нескольких таблиц в одну. Объединение происходит временное и целостность таблиц не нарушается. Существует три типа join-выражений:

• inner join;
• outer join;
• cross join;

В свою очередь, outer join может быть left, right и full (слово outer обычно опускается).

В качестве примера (DBMS Oracle) создадим две простые таблицы и сконструируем для них SQL-выражения с использованием join .

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

Содержимое таблиц пусть будет таким:

Конструкция join выглядит так:

. join_type join table_name on condition .

Где join_type — тип join-выражения, table_name — имя таблицы, которая присоединяется к результату, condition — условие объединения таблиц.

Кострукция join располагается сразу после select-выражения. Можно использовать несколько таких конструкций подряд для объединения соответствующего кол-ва таблиц. Логичнее всего использовать join в том случае, когда таблица имеет внешний ключ ( foreign key ).

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

Результат будет таким:

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

Результат выполнения запроса:

Результат показывает все ресурсы и их администраторов, вне зависимотсти от того есть они или нет.

Right join отображает все строки удовлетворяющие правой части условия condition , даже если они не имеют соответствия в главной (левой) таблице:

А результат будет следующим:

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

Full outer join (ключевое слово outer можно опустить) необходим для отображения всех возможных комбинаций строк из нескольких таблиц. Иными словами, это объединение результатов left и right join .

А результат будет таким:

Некоторые СУБД не поддерживают такую функциональность (например, MySQL), в таких случаях обычно используют объединение двух запросов:

Наконец, cross join. Этот тип join еще называют декартовым произведением (на английском — cartesian product). Настоятельно рекомендую использовать его с умом, так как время выполнения запроса с увеличением числа таблиц и строк в них растет нелинейно. Вот пример запроса, который аналогичен cross join :

Агрегатные функции, группировка данных

Для группировки данных в запросе select используется конструкция group by,
в которой должны быть перечислены те же столбцы, что и после select. Ниже приведен
пример вывода данных по группам для таблицы bills.

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

• avg([DISTINCT|ALL] column) — среднее значение по указанному столбцу;
• count(*|[DISTINCT|ALL] соlumn) — количество элементов в выборке
  или в группе определяемой указанным столбцом;
• sum([DISTINCT | ALL] соlumn) — сумма значений указанного столбца;
• max(соlumn) — максимальное значение в столбце;
• min(соlumn) — минимальное значение в столбце.

Ключевое слово DISTINCT позволяет игнорировать повторные значения в столбце, ALL
обрабатывает все значения в столбце (по умолчанию), * позволяет включить в обработку поля с null значением.
В MySQL между именем функции и скобкой не должно быть пробелов.
Ниже приведен пример использования агрегатных функций в качестве выбираемых данных. Если
агрегатная функция используется в выборке без group by, то она применяется ко всем записям
выборки, иначе для каждой группы в отдельности. И в любом случае в перечислении select нельзя
смешивать групповые столбцы с не групповыми.

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

Операции над выборками

Так как выборка по сути является множеством, то и доступные операции над ними
соответствующие:

• UNION — объединение, в конечной выборке записи из обоих запросов;
• INTERSECT — пересечение, в конечной выборке записи входящие в оба запроса;
• EXCEPT — исключение, в конечной выборке записи входящие только в первый запрос.

Запросы участвующие в таких операциях должны следовать нескольким условиям.
Иметь одинаковое число столбцов, соответствующие столбцы должны быть одного типа.
Тип данных столбца должен быть простым, т.е. не разрешаются типы подобные blob.
MySQL 5 поддерживает только UNION, в Oracle EXCEPT для других целей,
а для исключения используется MINUS.

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

Добавление итогов в SQL

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

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

А теперь предположим мы хотим вывести все суммы с итогами по каждой группе и в конце выборки
общий итог. Ниже приведен пример с использованием объединений. Чтобы общий итог был точно в
конце выборки, задаем в поле d максимальню дату. В Oracle и Postgre можно оставить значение
null.

Подобную задачу можно решить с помощью стандартной конструкции CUBE, если она уже
реализована в СУБД. Куб генерирует не только общий итог, но и все возможные под итоги.
Ниже приведен пример использования куба. Для упрощения кода пустые значения не заменяются.

Нумерация записей

В стандарт SQL2003 уже добавлена функция row_number(), если она еще не реализована
в вашей версии БД, используйте следующие методы.

Oracle

В Oracle для нумерации записей введен псевдостолбец rownum.

MySQL

В MySQL для этого надо воспользоваться переменной. Чтобы увидеть результат следующего
примера в MySQLQueryBrowser, необходимо начать транзакцию (на панели кнопка после слова
Transaction). Далее выполняем приведенные в примере команды и затем завершаем транзакцию
(соседняя кнопка с галочкой).

PostgreSQL

В PostgreSQL для этих целей можно выделить последовательность и сбрасывать ее перед новой
выборкой.