Найти дубликаты записей в MySQL

574

Я хочу вытащить дубликаты записей в базе данных MySQL. Это можно сделать с помощью:

SELECT address, count(id) as cnt FROM list
GROUP BY address HAVING cnt > 1

Результат:

100 MAIN ST    2

Я хотел бы потянуть его так, чтобы он показывал каждую строку, которая является дубликатом. Что-то вроде:

JIM    JONES    100 MAIN ST
JOHN   SMITH    100 MAIN ST

Любые мысли о том, как это можно сделать? Я пытаюсь не делать первый, а затем искать дубликаты со вторым запросом в коде.

Теги:
duplicates

20 ответов

591
Лучший ответ

Ключ должен переписать этот запрос так, чтобы он мог использоваться как подзапрос.

SELECT firstname, 
   lastname, 
   list.address 
FROM list
   INNER JOIN (SELECT address
               FROM   list
               GROUP  BY address
               HAVING COUNT(id) > 1) dup
           ON list.address = dup.address;
  • 67
    Будьте осторожны с подзапросами. Подзапросы являются / могут быть смехотворно плохими из-за проблем с производительностью. Если это должно происходить часто и / или с множеством повторяющихся записей, я бы рассмотрел возможность перемещения обработки из базы данных в набор данных.
  • 10
    Это некоррелированный подзапрос, поэтому он не должен быть слишком плохим, если только один запрос не является плохо спроектированным.
Показать ещё 9 комментариев
263
SELECT date FROM logs group by date having count(*) >= 2
  • 5
    Это был самый простой рабочий запрос для использования с Laravel. Просто нужно было добавить ->having(DB::raw('count(*)'), '>', 2) к запросу. Большое спасибо!
  • 1
    Хорошо работает с таблицей 10 миллионов строк. Это должен быть лучший ответ
Показать ещё 5 комментариев
177

Почему не просто INNER ПРИСОЕДИНИТЬСЯ к таблице с собой?

SELECT a.firstname, a.lastname, a.address
FROM list a
INNER JOIN list b ON a.address = b.address
WHERE a.id <> b.id

Требуется DISTINCT, если адрес может существовать более двух раз.

  • 20
    Я тоже это проверял, и это было почти в 6 раз медленнее по сравнению с принятым в моей ситуации решением (последний MySQL, таблица из 120 000 строк). Это может быть связано с тем, что требуется временная таблица, запустите EXPLAIN для обоих, чтобы увидеть различия.
  • 3
    Я изменил последнюю часть запроса на WHERE a.id > b.id чтобы отфильтровывать только новые дубликаты, таким образом я могу выполнить DELETE непосредственно по результату. Переключите сравнение, чтобы получить список старых дубликатов.
Показать ещё 3 комментария
45

Я попробовал лучший ответ, выбранный для этого вопроса, но это несколько смутило меня. Мне на самом деле нужно было только на одном поле из моего стола. Следующий пример из этой ссылки очень хорошо работал у меня:

SELECT COUNT(*) c,title FROM `data` GROUP BY title HAVING c > 1;
  • 0
    Работает как шарм!
36
select `cityname` from `codcities` group by `cityname` having count(*)>=2

Это тот же запрос, который вы запросили, и его 200% -ная работа и легко. Наслаждайтесь!!!

31

Найти дубликатов пользователей по адресу электронной почты с помощью этого запроса...

SELECT users.name, users.uid, users.mail, from_unixtime(created)
FROM users
INNER JOIN (
  SELECT mail
  FROM users
  GROUP BY mail
  HAVING count(mail) > 1
) dupes ON users.mail = dupes.mail
ORDER BY users.mail;
  • 2
    Чтобы найти дубликат, вам нужен только внутренний запрос. Это намного быстрее, чем другие ответы.
16

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

SELECT COUNT(*), column1, column2 
FROM tablename
GROUP BY column1, column2
HAVING COUNT(*)>1;
12

Другим решением будет использование псевдонимов таблицы, например:

SELECT p1.id, p2.id, p1.address
FROM list AS p1, list AS p2
WHERE p1.address = p2.address
AND p1.id != p2.id

Все, что вы действительно делаете в этом случае, - это взять исходную таблицу списков, создав две таблицы p - p 1 и p 2 - из этого, а затем выполните соединение в столбце адреса (строка 3). 4-я строка гарантирует, что одна и та же запись не будет отображаться несколько раз в вашем наборе результатов ( "дублировать дубликаты" ).

  • 1
    Работает хорошо. Если ГДЕ проверяется как LIKE, то также обнаруживаются апострофы. Делает запрос медленнее, но в моем случае это таймер.
11

Поиск дублирующих адресов намного сложнее, чем кажется, особенно если вам нужна точность. В этом случае запроса MySQL недостаточно...

Я работаю в SmartyStreets, где мы обращаемся к валидации и дедупликации и другим материалам, и я видел множество разнообразных проблем с подобными проблемами.

Существует несколько сторонних служб, которые будут отмечать дубликаты в списке для вас. Выполнение этого только с подзапросом MySQL не будет учитывать различия в форматах и ​​стандартах адресов. У USPS (для адреса США) есть определенные рекомендации по составлению этих стандартов, но только несколько поставщиков сертифицированы для выполнения таких операций.

Итак, я бы рекомендовал, что лучшим ответом для вас является экспорт таблицы в CSV файл, например, и отправку его в удобный процессор списка. Одним из таких является LiveAddress, который будет выполнен для вас за несколько секунд до нескольких минут автоматически. Он будет отмечать повторяющиеся строки с новым полем "Дубликат" и значением Y в нем.

  • 5
    +1 за то, что вы видите трудности, связанные с сопоставлением адресных строк, хотя вы, возможно, захотите указать, что вопрос OP «дубликаты записей» сам по себе не сложен, а связан со сравнением адресов.
10

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

SELECT  *
FROM    (
        SELECT  ao.*, (@r := @r + 1) AS rn
        FROM    (
                SELECT  @_address := 'N'
                ) vars,
                (
                SELECT  *
                FROM
                        list a
                ORDER BY
                        address, id
                ) ao
        WHERE   CASE WHEN @_address <> address THEN @r := 0 ELSE 0 END IS NOT NULL
                AND (@_address := address ) IS NOT NULL
        ) aoo
WHERE   rn > 1

Этот запрос актуализирует ROW_NUMBER(), присутствующий в Oracle и SQL Server

Подробнее см. статью в моем блоге:

  • 19
    Не для придирки, но FROM (SELECT ...) aoo это подзапрос :-P
8

Не будет очень эффективным, но он должен работать:

SELECT *
FROM list AS outer
WHERE (SELECT COUNT(*)
        FROM list AS inner
        WHERE inner.address = outer.address) > 1;
4

Это также покажет вам, сколько дубликатов имеет и будет заказывать результаты без объединений

SELECT  `Language` , id, COUNT( id ) AS how_many
FROM  `languages` 
GROUP BY  `Language` 
HAVING how_many >=2
ORDER BY how_many DESC
  • 0
    идеально, потому что он все еще говорит, сколько записей дублируется
  • 1
    Именно это моя точка зрения.
4

Лично этот запрос решил мою проблему:

SELECT `SUB_ID`, COUNT(SRV_KW_ID) as subscriptions FROM `SUB_SUBSCR` group by SUB_ID, SRV_KW_ID HAVING subscriptions > 1;

Что это за script показывает все идентификатор подписчика, который существует более одного раза в таблице, и количество найденных дубликатов.

Это столбцы таблицы:

| SUB_SUBSCR_ID | int(11)     | NO   | PRI | NULL    | auto_increment |
| MSI_ALIAS     | varchar(64) | YES  | UNI | NULL    |                |
| SUB_ID        | int(11)     | NO   | MUL | NULL    |                |    
| SRV_KW_ID     | int(11)     | NO   | MUL | NULL    |                |

Надеюсь, это будет полезно и вам!

4

Самая быстрая процедура удаления запросов дубликатов:

/* create temp table with one primary column id */
INSERT INTO temp(id) SELECT MIN(id) FROM list GROUP BY (isbn) HAVING COUNT(*)>1;
DELETE FROM list WHERE id IN (SELECT id FROM temp);
DELETE FROM temp;
  • 2
    Это, очевидно, удаляет только первую запись из каждой группы дубликатов.
4
select * from table_name t1 inner join (select distinct <attribute list> from table_name as temp)t2 where t1.attribute_name = t2.attribute_name

Для вашей таблицы это будет что-то вроде

select * from list l1 inner join (select distinct address from list as list2)l2 where l1.address=l2.address

Этот запрос предоставит вам все отдельные записи в таблице списка... Я не уверен, как это будет работать, если у вас есть значения первичного ключа для имени и т.д.

4
 SELECT firstname, lastname, address FROM list
 WHERE 
 Address in 
 (SELECT address FROM list
 GROUP BY address
 HAVING count(*) > 1)
  • 0
    Пробовал и этот тоже, но вроде просто завис. Поверьте, что возврат из внутреннего запроса не удовлетворяет формату параметра IN.
  • 0
    Что вы имеете в виду не удовлетворяет в формате параметра? Все, что нужно для IN - это чтобы ваш подзапрос возвращал один столбец. Это действительно довольно просто. Скорее всего, ваш подзапрос генерируется в столбце, который не проиндексирован, поэтому для его выполнения требуется слишком много времени. Я хотел бы предложить, если это займет много времени, чтобы разбить его на два запроса. Возьмите подзапрос, сначала запустите его во временную таблицу, создайте для него индекс, а затем выполните полный запрос, выполнив подзапрос, где ваше дублирующее поле во временной таблице.
Показать ещё 1 комментарий
3
SELECT t.*,(select count(*) from city as tt where tt.name=t.name) as count FROM `city` as t where (select count(*) from city as tt where tt.name=t.name) > 1 order by count desc

Замените город на таблицу. Замените имя на имя своего поля

2
    SELECT *
    FROM (SELECT  address, COUNT(id) AS cnt
    FROM list
    GROUP BY address
    HAVING ( COUNT(id) > 1 ))
0

Ответ Powerlord действительно лучший, и я бы порекомендовал еще одно изменение: используйте LIMIT, чтобы убедиться, что db не будет перегружен:

SELECT firstname, lastname, list.address FROM list
INNER JOIN (SELECT address FROM list
GROUP BY address HAVING count(id) > 1) dup ON list.address = dup.address
LIMIT 10

Хорошая привычка использовать LIMIT, если нет ГДЕ и при создании объединений. Начните с малого значения, проверьте, насколько тяжелен запрос, а затем увеличьте предел.

-1

select address from list where address = any (select address from (select address, count(id) cnt from list group by address having cnt > 1 ) as t1) order by address

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

Ещё вопросы

Сообщество Overcoder
Наверх
Меню