Как найти одинаковые строки в таблице без первичного ключа и без уникальных значений в столбцах?
Как найти и отобразить одинаковые строки (по всем полям) в таблице, не имеющую первичный ключ и уникальные значения полей? Желателен код по стандарту ANSI SQL, не зависящий от СУБД.
Отслеживать
задан 5 сен 2018 в 16:55
5,491 5 5 золотых знаков 46 46 серебряных знаков 90 90 бронзовых знаков
Вам нужно что? Посчитать их или отобразить? Отобразить в каком виде? «Одинаковые» по конкретному полю?
5 сен 2018 в 17:11
Отобразить их, одинаковые по всем полям.
5 сен 2018 в 17:49
и много в таблице полей?
5 сен 2018 в 17:51
ну столбцов примерно штук 20
5 сен 2018 в 17:57
Ну одним запросом будет сложно)))
5 сен 2018 в 18:19
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
Пример для таблицы с двумя столбцами
SELECT Field1, Field2 FROM Table1 GROUP BY Field1, Field2 HAVING ( COUNT(*) > 1 )
upd:обязательное перечисление всех полей
Отслеживать
ответ дан 5 сен 2018 в 20:22
5,491 5 5 золотых знаков 46 46 серебряных знаков 90 90 бронзовых знаков
При частичном совпадении, когда часть полей совпадает, а часть нет, результат может быть неверным
5 сен 2018 в 20:25
Если все поля перечислить, то будет норм.
6 сен 2018 в 8:34
Вывести все строки таблицы MY_TABLE, у которых есть одинаковые значения поля REPEAT_FIELD:
SELECT * FROM MY_TABLE T1 INNER JOIN ( SELECT REPEAT_FIELD, COUNT(*) AS CNT FROM MY_TABLE GROUP BY REPEAT_FIELD ) AS T2 ON T1.REPEAT_FIELD = T2.REPEAT_FIELD WHERE T2.CNT > 1
синтаксис от mySQL, но вроде стандартно все
Отслеживать
ответ дан 5 сен 2018 в 17:21
Dmitry Kozlov Dmitry Kozlov
6,698 2 2 золотых знака 15 15 серебряных знаков 30 30 бронзовых знаков
мне кажется лучше в подзапросе сделать having count(*)>1, что бы отсечь не нужные записи до join
5 сен 2018 в 17:29
можно и так, добавьте свой вариант как ответ с пояснением
5 сен 2018 в 17:31
- sql
- стандарт
- повторы
- дублирование
-
Важное на Мете
Похожие
Подписаться на ленту
Лента вопроса
Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.
Дизайн сайта / логотип © 2023 Stack Exchange Inc; пользовательские материалы лицензированы в соответствии с CC BY-SA . rev 2023.11.15.1019
Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.
Операторы Set — EXCEPT и INTERSECT (Transact-SQL)
Эти операторы возвращают различные строки, сравнивая результаты двух запросов.
Оператор EXCEPT возвращает уникальные строки из левого входного запроса, которые не выводятся правым входным запросом.
Оператор INTERSECT возвращает уникальные строки, выводимые левым и правым входными запросами.
Основные правила объединения результирующих наборов двух запросов с оператором EXCEPT или INTERSECT таковы:
- количество и порядок столбцов должны быть одинаковыми во всех запросах;
- типы данных должны быть совместимыми.
Синтаксис
Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
query_specification> | ( query_expression> )
Спецификация запроса или выражение запроса, возвращающее данные для сравнения с данными, возвращенными другой спецификацией запроса или выражением запроса. Определения столбцов, обрабатываемых при операции EXCEPT или INTERSECT, могут быть разными. Тем не менее они должны поддерживать возможность сравнения путем неявного преобразования типов. Если типы данных различаются, то тип данных для сравнения определяется на основе правил очередности типов данных.
Если типы одинаковы, но различаются по точности, масштабу или длине, результат определяется на основе тех же самых правил, которые действуют при объединении выражений. Дополнительные сведения см. в разделе Точность, масштаб и длина (Transact-SQL).
Спецификация или выражение запроса не может возвращать столбцы типа xml, text, ntext, image или недвоичного пользовательского типа данных CLR, потому что эти типы данных не поддерживают сравнение.
ИСКЛЮЧЕНИЯ
Возвращает все различные значения, возвращенные запросом, указанным слева от оператора EXCEPT. Эти значения возвращаются, если они отсутствуют в результатах выполнения правого запроса.
INTERSECT
Возвращает все различные значения, входящие в результаты выполнения запросов, указанных как слева, так и справа от оператора INTERSECT.
Замечания
Типы данных сравниваемых столбцов возвращаются запросами слева и справа от операторов EXCEPT или INTERSECT. Эти типы данных могут содержать символьные типы данных с различными параметрами сортировки. При этом необходимое сравнение выполняется в соответствии с правилами очередности параметров сортировки. Если вы не можете выполнить это преобразование, sql Server ядро СУБД возвращает ошибку.
Если сравниваются значения столбцов с целью определения различных строк, два значения NULL считаются равными.
Операторы EXCEPT и INTERSECT возвращают имена столбцов результирующего набора, совпадающие с именами столбцов, которые возвращает запрос слева от оператора.
Имена столбцов или псевдонимы в предложениях ORDER BY должны ссылаться на имена столбцов, возвращаемых запросом, указанным слева от оператора.
Возможность хранения пустых значений в каком-либо столбце результирующего набора, возвращаемого оператором EXCEPT или INTERSECT, зависит от того, поддерживает ли это соответствующий столбец, возвращаемый запросом, указанным слева от оператора.
Если оператор EXCEPT или INTERSECT используется в выражении вместе с другими операторами, оно обрабатывается в следующем порядке:
- Выражения в скобках
- Оператор INTERSECT
- Операторы EXCEPT и UNION обрабатываются слева направо в соответствии с их позицией в выражении.
Оператор EXCEPT или INTERSECT можно использовать для сравнения более двух наборов запросов. При этом преобразование типов данных выполняется на основе сравнения двух запросов сразу с соблюдением вышеупомянутых правил обработки выражений.
Операторы EXCEPT и INTERSECT нельзя использовать в определениях распределенных секционированных представлений и уведомлениях о запросах.
Операторы EXCEPT и INTERSECT можно применять в распределенных запросах, но они будут выполнены только на локальном сервере и не будут распространены на связанный сервер. Таким образом, использование операторов EXCEPT и INTERSECT в распределенных запросах может сказаться на производительности.
При выполнении операции EXCEPT или INTERSECT в результирующем наборе можно использовать быстрые однонаправленные и статические курсоры. В операции EXCEPT и INTERSECT можно использовать курсор, управляемый набором ключей, или динамический курсор. В таком случае курсор результирующего набора преобразуется в статический курсор.
Если операция EXCEPT отображается с помощью функции графического шоуплана в СРЕДЕ SQL Server Management Studio, операция отображается как левое антизаверховое соединение, а операция INTERSECT отображается как левое полусоединение.
Примеры
В следующих примерах демонстрируется использование операторов INTERSECT и EXCEPT . Первый запрос возвращает все значения из таблицы Production.Product для сравнения с результатами, полученными с операндами INTERSECT и EXCEPT .
-- Uses AdventureWorks SELECT ProductID FROM Production.Product ; --Result: 504 Rows
Следующий запрос возвращает все различные значения, входящие в результаты выполнения, как левого, так и правого запроса оператора INTERSECT .
-- Uses AdventureWorks SELECT ProductID FROM Production.Product INTERSECT SELECT ProductID FROM Production.WorkOrder ; --Result: 238 Rows (products that have work orders)
Следующий запрос возвращает все уникальные значения, возвращенные запросом, указанным слева от оператора EXCEPT , но отсутствующие в результатах выполнения правого запроса.
-- Uses AdventureWorks SELECT ProductID FROM Production.Product EXCEPT SELECT ProductID FROM Production.WorkOrder ; --Result: 266 Rows (products without work orders)
Следующий запрос возвращает все уникальные значения, возвращенные запросом, указанным слева от оператора EXCEPT , но отсутствующие в результатах выполнения правого запроса. Таблицы расположены в порядке, обратном предыдущему примеру.
-- Uses AdventureWorks SELECT ProductID FROM Production.WorkOrder EXCEPT SELECT ProductID FROM Production.Product ; --Result: 0 Rows (work orders without products)
Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)
Следующий пример демонстрирует использование операторов INTERSECT и EXCEPT . Первый запрос возвращает все значения из таблицы FactInternetSales для сравнения с результатами, полученными с операндами INTERSECT и EXCEPT .
-- Uses AdventureWorks SELECT CustomerKey FROM FactInternetSales; --Result: 60398 Rows
Следующий запрос возвращает все различные значения, входящие в результаты выполнения, как левого, так и правого запроса оператора INTERSECT .
-- Uses AdventureWorks SELECT CustomerKey FROM FactInternetSales INTERSECT SELECT CustomerKey FROM DimCustomer WHERE DimCustomer.Gender = 'F' ORDER BY CustomerKey; --Result: 9133 Rows (Sales to customers that are female.)
Следующий запрос возвращает все уникальные значения, возвращенные запросом, указанным слева от оператора EXCEPT , но отсутствующие в результатах выполнения правого запроса.
-- Uses AdventureWorks SELECT CustomerKey FROM FactInternetSales EXCEPT SELECT CustomerKey FROM DimCustomer WHERE DimCustomer.Gender = 'F' ORDER BY CustomerKey; --Result: 9351 Rows (Sales to customers that are not female.)
Обратная связь
Были ли сведения на этой странице полезными?
SQL. Выборка одинаковых значений по одной колонке, но разной по другой. Как реализовать?
Нужно выбрать товары product_id, который есть у разных пользователей.
То есть, товар 16 купил пользователь 1,2,3, его выьираем, а товар 15, хоть он и повторяется купил один и тотже пользователь с id 1.
Пробовал писать что-то такое:
SELECT product_id FROM basket GROUP BY product_id HAVING count(*)>1;
Но находятся просто одинковые записи. Не могу сделать так, чтобы искались одинаковые product_id, но при этом с разными user_id.
Любая помощь приветсвуется.
- Вопрос задан более трёх лет назад
- 17648 просмотров
Комментировать
Решения вопроса 1
Готовые решения — не подаю, но.
Если MySQL умеет distinct в count, то
SELECT product_id FROM basket GROUP BY product_id HAVING count(distinct user_id)>1
Нахождение повторяющихся значений в таблице в MySQL
Избыточные данные могут храниться в таблице программой базы данных, влияя на вывод базы данных в MySQL. Однако репликация данных выполняется для разных целей, и идентификация повторяющихся значений в таблице является важной задачей при работе с базой данных MySQL. В общем, разумно часто использовать четкие ограничения для таблицы, чтобы хранить информацию, которая предотвращает появление избыточных строк. Иногда в базе данных MySQL вам может потребоваться подсчитать количество повторяющихся значений. Мы рассмотрели этот вопрос в этой теме, в которой вы узнаете, как находить повторяющиеся значения разными способами и как подсчитывать повторяющиеся значения.
Для начала у вас должен быть установлен MySQL в вашей системе со своими утилитами: рабочая среда MySQL и клиентская оболочка командной строки. После этого у вас должны быть дубликаты некоторых данных или значений в таблицах базы данных. Давайте рассмотрим это на нескольких примерах. Прежде всего, откройте клиентскую оболочку командной строки с панели задач рабочего стола и введите свой пароль MySQL по запросу.
Мы нашли разные методы поиска дубликатов в таблице. Взгляните на них один за другим.
Поиск дубликатов в одном столбце
Во-первых, вы должны знать синтаксис запроса, используемого для проверки и подсчета дубликатов для одного столбца.
>> SELECT col COUNT(col) FROM table GROUP BY col HAVING COUNT(col) > 1;
Вот объяснение вышеуказанного запроса:
- Столбец: имя проверяемого столбца.
- COUNT(): функция, используемая для подсчета множества повторяющихся значений.
- GROUP BY: предложение, используемое для группировки всех строк в соответствии с этим конкретным столбцом.
Мы создали новую таблицу под названием «animals» в «data» нашей базы данных MySQL, имеющую повторяющиеся значения. Он имеет шесть столбцов с разными значениями, например, id, Name, Species, Gender, Age и Price, предоставляя информацию о различных домашних животных. После вызова этой таблицы с помощью запроса SELECT мы получаем следующий вывод в нашей клиентской оболочке командной строки MySQL.
>> SELECT * FROM data.animals;
Читать Оптимизация и восстановление баз данных MySQL с помощью mysqlcheck
Теперь мы попытаемся найти повторяющиеся и повторяющиеся значения из приведенной выше таблицы, используя функцию COUNT и GROUP BY в запросе SELECT. Этот запрос будет считать имена домашних животных, которые встречаются в таблице менее трех раз. После этого он отобразит эти имена, как показано ниже.
>> SELECT Name COUNT(Name) FROM data.animals GROUP BY Name HAVING COUNT(Name) < 3;
Использование того же запроса для получения разных результатов при изменении числа COUNT для имен домашних животных, как показано ниже.
>> SELECT Name COUNT(Name) FROM data.animals GROUP BY Name HAVING COUNT(Name) > 3;
Чтобы получить результаты для 3 повторяющихся значений для имен домашних животных, как показано ниже.
>> SELECT Name COUNT(Name) FROM data.animals GROUP BY Name HAVING COUNT(Name) = 3;
Искать дубликаты в нескольких столбцах
Синтаксис запроса для проверки или подсчета дубликатов для нескольких столбцов следующий:
>> SELECT col1, COUNT(col1), col2, COUNT(col2) FROM table GROUP BY col1, col2 HAVING COUNT(col1) > 1 AND COUNT(col2) > 1;
Вот объяснение вышеуказанного запроса:
- col1, col2: имя проверяемых столбцов.
- COUNT(): функция, используемая для подсчета нескольких повторяющихся значений.
- GROUP BY: предложение, используемое для группировки всех строк в соответствии с этим конкретным столбцом.
Мы использовали ту же таблицу под названием «животные» с повторяющимися значениями. Мы получили приведенный ниже результат, используя указанный выше запрос для проверки повторяющихся значений в нескольких столбцах. Мы проверяли и подсчитывали повторяющиеся значения для столбцов «Gender» и «Price», сгруппированные по столбцу «Price». Он покажет пол домашних животных и их цены, которые находятся в таблице, как дубликаты не более 5.
>> SELECT Gender, COUNT(Gender), Price, COUNT(Price) FROM data.animals GROUP BY Price HAVING COUNT(Price) < 5 AND COUNT(Gender) < 5;
Поиск дубликатов в одной таблице с помощью INNER JOIN
Вот основной синтаксис для поиска дубликатов в одной таблице:
>> SELECT col1, col2, table.col FROM table INNER JOIN(SELECT col FROM table GROUP BY col HAVING COUNT(col1) > 1) temp ON table.col= temp.col;
Вот описание служебного запроса:
- Col: имя столбца, который нужно проверить и выбрать для дублирования.
- Temp: ключевое слово для применения внутреннего соединения к столбцу.
- Таблица: имя проверяемой таблицы.
У нас есть новая таблица order2 с повторяющимися значениями в столбце OrderNo, как показано ниже.
>> SELECT * FROM data.order2;
Читать NULL в MySQL. Руководство для начинающих
Мы выбираем три столбца: Item, Sales, OrderNo, которые будут отображаться в выводе. В то время как столбец OrderNo используется для проверки дубликатов. Внутреннее соединение выберет значения или строки, имеющие значения элементов более одного в таблице. После выполнения мы получим следующие результаты.
>> SELECT Item, Sales, order2.OrderNo FROM data.order2 INNER JOIN(SELECT OrderNo FROM data.order2 GROUP BY OrderNo HAVING COUNT(Item) > 1) temp ON order2.OrderNo= temp.OrderNo;
Поиск дубликатов в нескольких таблицах с помощью INNER JOIN
Вот упрощенный синтаксис для поиска дубликатов в нескольких таблицах:
>> SELECT col FROM table1 INNER JOIN table2 ON table1.col = table2.col;
Вот описание служебного запроса:
- col: имя столбцов, которые нужно проверить и выбрать.
- INNER JOIN: функция, используемая для соединения двух таблиц.
- ВКЛ: используется для объединения двух таблиц в соответствии с предоставленными столбцами.
У нас есть две таблицы, «order1» и «order2», в нашей базе данных со столбцом «OrderNo» в обеих, как показано ниже.
Мы будем использовать INNER join для объединения дубликатов двух таблиц в соответствии с указанным столбцом. Предложение INNER JOIN получит все данные из обеих таблиц, объединив их, а предложение ON будет связывать столбцы с одинаковыми именами из обеих таблиц, например, OrderNo.
>> SELECT * FROM data.order1 INNER JOIN data.order2 ON order1.OrderNo = order2.OrderNO;
Чтобы получить определенные столбцы в выходных данных, попробуйте следующую команду:
>> SELECT Region, Status, Item, Sales FROM data.order1 INNER JOIN data.order2 ON order1.OrderNo = order2.OrderNO;
Вывод
Теперь мы могли искать несколько копий в одной или нескольких таблицах информации MySQL и распознавать функции GROUP BY, COUNT и INNER JOIN. Убедитесь, что вы правильно построили таблицы и что выбраны правильные столбцы.
Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.