Sql как найти повторяющиеся значения в столбце

try this:

declare @YourTable table (id int, name varchar(10), email varchar(50))

INSERT @YourTable VALUES (1,'John','John-email')
INSERT @YourTable VALUES (2,'John','John-email')
INSERT @YourTable VALUES (3,'fred','John-email')
INSERT @YourTable VALUES (4,'fred','fred-email')
INSERT @YourTable VALUES (5,'sam','sam-email')
INSERT @YourTable VALUES (6,'sam','sam-email')

SELECT
    name,email, COUNT(*) AS CountOf
    FROM @YourTable
    GROUP BY name,email
    HAVING COUNT(*)>1

OUTPUT:

name       email       CountOf
---------- ----------- -----------
John       John-email  2
sam        sam-email   2

(2 row(s) affected)

if you want the IDs of the dups use this:

SELECT
    y.id,y.name,y.email
    FROM @YourTable y
        INNER JOIN (SELECT
                        name,email, COUNT(*) AS CountOf
                        FROM @YourTable
                        GROUP BY name,email
                        HAVING COUNT(*)>1
                    ) dt ON y.name=dt.name AND y.email=dt.email

OUTPUT:

id          name       email
----------- ---------- ------------
1           John       John-email
2           John       John-email
5           sam        sam-email
6           sam        sam-email

(4 row(s) affected)

to delete the duplicates try:

DELETE d
    FROM @YourTable d
        INNER JOIN (SELECT
                        y.id,y.name,y.email,ROW_NUMBER() OVER(PARTITION BY y.name,y.email ORDER BY y.name,y.email,y.id) AS RowRank
                        FROM @YourTable y
                            INNER JOIN (SELECT
                                            name,email, COUNT(*) AS CountOf
                                            FROM @YourTable
                                            GROUP BY name,email
                                            HAVING COUNT(*)>1
                                        ) dt ON y.name=dt.name AND y.email=dt.email
                   ) dt2 ON d.id=dt2.id
        WHERE dt2.RowRank!=1
SELECT * FROM @YourTable

OUTPUT:

id          name       email
----------- ---------- --------------
1           John       John-email
3           fred       John-email
4           fred       fred-email
5           sam        sam-email

(4 row(s) affected)

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

SELECT
	`col1`,
	COUNT(`col1`) AS `count`
FROM
	`table`
GROUP BY
	`col1`
HAVING 
	`count` > 1

SELECT
	`col1`,
	`col2`,
	COUNT(*) AS `count`
FROM
	`table`
GROUP BY
	`col1`,`col2`
HAVING 
	`count` > 1

SELECT 
	*
FROM 
	`table`
WHERE 
	`col1` IN (SELECT `col1` FROM `table` GROUP BY `col1` HAVING COUNT(*) > 1)
ORDER BY
	`col1`

SELECT 
	*
FROM 
	`table`
WHERE 
	`col1` IN (SELECT `col1` FROM `table` GROUP BY `col1` HAVING COUNT(*) > 1)
	AND `col2` IN (SELECT `col2` FROM `table` GROUP BY `col2` HAVING COUNT(*) > 1)
ORDER BY
	`col1`

SELECT 
	`table`.*
FROM
	`table`
LEFT OUTER JOIN 
	(SELECT MIN(`id`) AS `id`, `col1` FROM `table` GROUP BY `col1`) AS `tmp` 
ON 
	`table`.`id` = `tmp`.`id`  
WHERE
	`tmp`.`id` IS NULL

SELECT 
	`table`.*
FROM
	`table`
LEFT OUTER JOIN 
	(SELECT MIN(`id`) AS `id`, `col1`, `col2` FROM `table` GROUP BY `col1`, `col2`) AS `tmp` 
ON 
	`a`.`id` = `tmp`.`id`  
WHERE
	`tmp`.`id` IS NULL

UPDATE  
	`table`
LEFT OUTER JOIN 
	(SELECT MIN(`id`) AS `id`, `col1` FROM `table` GROUP BY `col1`) AS `tmp` 
ON 
	`table`.`id` = `tmp`.`id`
SET
	`table`.`col1` = CONCAT(`table`.`col1`, '-', `table`.`id`)    
WHERE
	`tmp`.`id` IS NULL

UPDATE  
	`table`
LEFT OUTER JOIN 
	(SELECT MIN(`id`) AS `id`, `col1`, `col2` FROM `table` GROUP BY `col1`, `col2`) AS `tmp` 
ON 
	`table`.`id` = `tmp`.`id`
SET
	`table`.`col1` = CONCAT(`table`.`col1`, '-', `table`.`id`)    
WHERE
	`tmp`.`id` IS NULL

DELETE
	`table`
FROM  
	`table`
LEFT OUTER JOIN 
	(SELECT MIN(`id`) AS `id`, `col1` FROM `table` GROUP BY `col1`) AS `tmp` 
ON 
	`table`.`id` = `tmp`.`id`  
WHERE
	`tmp`.`id` IS NULL

DELETE
	`table`
FROM  
	`table`
LEFT OUTER JOIN 
	(SELECT MIN(`id`) AS `id`, `col1`, `col2` FROM `table` GROUP BY `col1`, `col2`) AS `tmp` 
ON 
	`table`.`id` = `tmp`.`id`  
WHERE
	`tmp`.`id` IS NULL

Избыточные данные могут храниться в таблице программой базы данных, влияя на вывод базы данных в 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;

Теперь мы попытаемся найти повторяющиеся и повторяющиеся значения из приведенной выше таблицы, используя функцию 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;

Мы выбираем три столбца: 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. Убедитесь, что вы правильно построили таблицы и что выбраны правильные столбцы.