Trova i record duplicati in MySQL

Voglio estrarre i record duplicati in un database MySQL. Questo può essere fatto con:

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

Quale risulta in:

 100 MAIN ST 2 

Mi piacerebbe tirarlo in modo che mostri ogni riga che è un duplicato. Qualcosa di simile a:

 JIM JONES 100 MAIN ST JOHN SMITH 100 MAIN ST 

Qualche idea su come questo può essere fatto? Sto cercando di evitare di fare il primo, quindi cercare i duplicati con una seconda query nel codice.

La chiave è riscrivere questa query in modo che possa essere utilizzata come sottoquery.

 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; 
 SELECT date FROM logs group by date having count(*) >= 2 

Perché non solo ENTRARE INSIEME al tavolo con se stesso?

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

Un DISTINCT è necessario se l’indirizzo potrebbe esistere più di due volte.

Ho provato la migliore risposta scelta per questa domanda, ma mi ha un po ‘confuso. In realtà ne avevo bisogno solo su un singolo campo del mio tavolo. Il seguente esempio da questo link ha funzionato molto bene per me:

 SELECT COUNT(*) c,title FROM `data` GROUP BY title HAVING c > 1; 
 select `cityname` from `codcities` group by `cityname` having count(*)>=2 

Questa è la query simile che hai richiesto e il suo 200% funziona e anche facile. Godere!!!

Trova utenti duplicati per indirizzo email con questa query …

 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; 

possiamo trovare che i duplicati dipendono anche da più di un campo. Per questi casi è ansible utilizzare il formato seguente.

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

Un’altra soluzione sarebbe quella di utilizzare alias di tabelle, in questo modo:

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

Tutto quello che stai facendo in questo caso è prendere la tabella list originale, creando due p retend tables – p 1 e p 2 – fuori da quello, e quindi eseguendo un join sulla colonna dell’indirizzo (riga 3). La quarta riga si assicura che lo stesso record non si visualizzi più volte nel set di risultati (“duplicati duplicati”).

Trovare indirizzi duplicati è molto più complesso di quanto sembri, soprattutto se si richiede precisione. In questo caso, una query MySQL non è sufficiente …

Lavoro in SmartyStreets , dove affrontiamo la validazione e la deduplicazione e altre cose, e ho visto molte sfide diverse con problemi simili.

Esistono diversi servizi di terze parti che contrassegneranno i duplicati in un elenco per te. Farlo solo con una subquery MySQL non terrà conto delle differenze nei formati e negli standard degli indirizzi. L’USPS (per gli indirizzi degli Stati Uniti) ha alcune linee guida per rendere questi standard, ma solo una manciata di fornitori sono certificati per eseguire tali operazioni.

Quindi, la soluzione migliore per te è quella di esportare la tabella in un file CSV, ad esempio, e inviarla a un elaboratore di elenchi capace. Uno di questi è LiveAddress che lo farà in pochi secondi automaticamente in pochi secondi. Contrassegna le righe duplicate con un nuovo campo chiamato “Duplicato” e un valore di Y in esso.

Ciò selezionerà i duplicati in un passaggio di tabella, senza sottoquery.

 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 

Questa query emula in modo ROW_NUMBER() presente in Oracle e SQL Server

Vedi l’articolo nel mio blog per i dettagli:

  • Funzioni analitiche: SUM, AVG, ROW_NUMBER – emulando in MySQL .

Non sarà molto efficiente, ma dovrebbe funzionare:

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

Questo ti mostrerà anche quanti duplicati hanno e ordinerà i risultati senza join

 SELECT `Language` , id, COUNT( id ) AS how_many FROM `languages` GROUP BY `Language` HAVING how_many >=2 ORDER BY how_many DESC 

Non è così facile:

 SELECT * FROM tc_tariff_groups GROUP BY group_id HAVING COUNT(group_id) >1 

?

  SELECT firstname, lastname, address FROM list WHERE Address in (SELECT address FROM list GROUP BY address HAVING count(*) > 1) 
 select * from table_name t1 inner join (select distinct  from table_name as temp)t2 where t1.attribute_name = t2.attribute_name 

Per il tuo tavolo sarebbe qualcosa di simile

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

Questa query ti fornirà tutte le voci di indirizzo distinte nella tabella elenco … Non sono sicuro di come funzionerà se hai valori di chiave primaria per nome, ecc.

Procedura di richiesta di rimozione dei duplicati più veloce:

 /* 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; 

Personalmente questa query ha risolto il mio problema:

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

Ciò che questo script fa mostra tutti gli ID iscritti che esistono più di una volta nella tabella e il numero di duplicati trovati.

Queste sono le colonne della tabella:

 | 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 | | 

Spero che sia utile anche per te!

 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 

Sostituisci la città con il tuo tavolo. Sostituisci il nome con il nome del tuo campo

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

La risposta del powerlord è davvero la migliore e consiglierei un’altra modifica: usa LIMIT per assicurarti che il db non venga sovraccaricato:

 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 

È buona norma usare LIMIT se non c’è WHERE e quando si effettuano i join. Inizia con un valore piccolo, controlla quanto è pesante la query e quindi aumenta il limite.

  Find duplicate Records: Suppose we have table : Student student_id int student_name varchar Records: +------------+---------------------+ | student_id | student_name | +------------+---------------------+ | 101 | usman | | 101 | usman | | 101 | usman | | 102 | usmanyaqoob | | 103 | muhammadusmanyaqoob | | 103 | muhammadusmanyaqoob | +------------+---------------------+ Now we want to see duplicate records Use this query: select student_name,student_id ,count(*) c from student group by student_id,student_name having c>1; +--------------------+------------+---+ | student_name | student_id | c | +---------------------+------------+---+ | usman | 101 | 3 | | muhammadusmanyaqoob | 103 | 2 | +---------------------+------------+---+ 

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

la subquery interna restituisce le righe con indirizzo duplicato, quindi la subquery esterna restituisce la colonna dell’indirizzo per l’indirizzo con duplicati. la sottoquery esterna deve restituire solo una colonna perché viene utilizzata come operando per l’operatore ‘= any’