Dieser Artikel zeigt, wie Sie doppelte Zeilen in einer Datenbanktabelle finden. Dies ist eine sehr häufige Anfängerfrage. Die grundlegende Technik ist unkompliziert. Ich werde auch einige Variationen zeigen, z. B. wie man „Duplikate in zwei Spalten“ findet (eine aktuelle Frage im IRC-Kanal #mysql).
So finden Sie doppelte Zeilen
Der erste Schritt besteht darin, zu definieren, was genau eine Zeile zu einem Duplikat einer anderen Zeile macht. Meistens ist dies einfach: Sie haben in einigen Spalten denselben Wert. Ich nehme dies als Arbeitsdefinition für diesen Artikel, aber Sie können Sie müssen die folgenden Abfragen ändern, wenn Ihre Vorstellung von „Duplizieren“ komplizierter ist.
Für diesen Artikel verwende ich die folgenden Beispieldaten:
Die ersten beiden Zeilen haben dieselben Wert in der Spalte day. Wenn ich diese also als Duplikate betrachte, finden Sie hier eine Abfrage, um sie zu finden. Die Abfrage verwendet eine GROUP BY -Klausel, um alle Zeilen mit demselben day -Wert in eine „Gruppe“ zu setzen und dann die Größe der zu zählen Gruppe:
Die Anzahl der duplizierten Zeilen ist größer als eins. Wenn Sie nur doppelte Zeilen anzeigen möchten, müssen Sie eine HAVING -Klausel (keine WHERE -Klausel), wie folgt:
Dies ist die grundlegende Technik: Gruppieren Sie nach der Spalte, die Duplikate enthält, und zeigen Sie nur die Gruppen mit mehr als einer Zeile an.
Warum können Sie keine WHERE-Klausel verwenden?
A. WHERE -Klausel filtert die Zeilen, bevor sie zusammen gruppiert werden. Eine HAVING -Klausel filtert sie nach der Gruppierung. Deshalb können Sie keine WHERE -Klausel in der obigen Abfrage.
So löschen Sie doppelte Zeilen
Eine verwandte Frage ist, wie Sie die ‚doppelten‘ Zeilen löschen, sobald Sie finde sie. Ein gemeinsames t Fragen Sie beim Bereinigen fehlerhafter Daten, ob Sie alle Duplikate bis auf eines löschen möchten, damit Sie die richtigen Indizes und Primärschlüssel in die Tabelle einfügen und verhindern können, dass Duplikate erneut in die Tabelle gelangen.
Wieder das erste Stellen Sie sicher, dass Ihre Definition klar ist. Welche Zeile möchten Sie genau behalten? Der erste? Der mit dem größten Wert einer Spalte? In diesem Artikel gehe ich davon aus, dass Sie die erste Zeile beibehalten möchten – die mit dem kleinsten Wert der Spalte id. Das bedeutet, dass Sie jede zweite Zeile löschen möchten.
Der wahrscheinlich einfachste Weg, dies zu tun, ist eine temporäre Tabelle. Insbesondere in MySQL gibt es einige Einschränkungen beim Auswählen und Aktualisieren aus einer Tabelle in derselben Abfrage. Sie können diese umgehen, wie ich in meinem Artikel erläutere. So wählen Sie aus einem Update-Ziel in MySQL aus, aber ich vermeide nur diese Komplikationen und verwende eine temporäre Tabelle.
Die genaue Definition der Aufgabe lautet um jede Zeile mit einem Duplikat zu löschen, mit Ausnahme der Zeile mit dem Mindestwert id für diese Gruppe. Sie müssen also nicht nur die Zeilen finden, in denen sich mehr als eine in der Gruppe befindet, sondern auch die Zeile, die Sie behalten möchten. Sie können dies mit der Funktion MIN() tun. Hier sind einige Abfragen, um die temporäre Tabelle zu erstellen und die Daten zu finden, die Sie für die DELETE benötigen:
Nachdem Sie diese Daten haben, können Sie mit dem Löschen fortfahren die „schlechten“ Zeilen. Es gibt viele Möglichkeiten, dies zu tun, und einige sind besser als andere (siehe meinen Artikel über viele-zu-eins-Probleme in SQL), aber ich werde auch hier die Feinheiten vermeiden und Ihnen nur eine Standardsyntax zeigen, die funktionieren sollte Alle RDBMS, die Unterabfragen unterstützen:
Wenn Ihr RDBMS keine Unterabfragen unterstützt oder effizienter ist, möchten Sie möglicherweise einen Löschvorgang für mehrere Tabellen durchführen. Die Syntax hierfür variiert zwischen den Systemen. Sie müssen daher die Dokumentation Ihres Systems konsultieren. Möglicherweise müssen Sie dies auch in einer Transaktion tun, um zu vermeiden, dass andere Benutzer die Daten während der Arbeit ändern, wenn dies ein Problem darstellt.
So finden Sie Duplikate in mehreren Spalten
Jemand hat kürzlich eine ähnliche Frage auf dem IRC-Kanal #mysql gestellt:
Ich habe eine Tabelle mit den Spalten
bundc, die zwei andere Tabellen verknüpfenbundc, und ich möchte um alle Zeilen mit Duplikaten inboderczu finden.
Es war schwierig, genau zu verstehen, was dies bedeutete, aber nach einigen Gesprächen begriff ich es: Die Person wollte eindeutige Indizes für die Spalten b und c separat.
Es ist ziemlich einfach, Zeilen mit doppelten Werten in der einen oder anderen Spalte zu finden, wie ich Ihnen oben gezeigt habe: Gruppieren Sie einfach nach dieser Spalte mn und zähle die Gruppengröße. Und es ist einfach, ganze Zeilen zu finden, die exakte Duplikate anderer Zeilen sind: Gruppieren Sie einfach nach so vielen Spalten, wie Sie benötigen.Es ist jedoch schwieriger, Zeilen zu identifizieren, die entweder einen doppelten b -Wert oder einen doppelten c -Wert haben. Nehmen Sie die folgende Beispieltabelle, die ungefähr der Beschreibung der Person entspricht:
Nun können Sie leicht erkennen, dass diese Tabelle einige ‚doppelte‘ Zeilen enthält, aber keine zwei Zeilen haben tatsächlich das gleiche Tupel {b, c}. Aus diesem Grund ist dies etwas schwieriger zu lösen.
Abfragen, die nicht funktionieren
Wenn Sie nach zwei Spalten gruppieren, erhalten Sie je nach Gruppierung unterschiedliche Ergebnisse und zählen. Hier wurde der IRC-Benutzer ratlos. Manchmal fanden Abfragen einige Duplikate, andere jedoch nicht. Hier sind einige der Dinge, die diese Person versucht hat:
Diese Abfrage gibt jede Zeile in der Tabelle mit einer COUNT(*) von 1, was ein falsches Verhalten zu sein scheint, aber eigentlich nicht. Warum? Weil sich die > 1 innerhalb der COUNT() befindet. Es ist ziemlich leicht zu übersehen, aber diese Abfrage entspricht der
Warum? Weil (b > 1) ein boolescher Ausdruck ist. Das wollen Sie überhaupt nicht. Sie möchten
Dies gibt natürlich null Zeilen zurück, da es keine doppelten {b, c} -Tupel gibt. Die Person versuchte viele andere Kombinationen von HAVING -Klauseln und ORs und ANDs, gruppierte nach einer Spalte und zählte die andere und so weiter:
Es wurden jedoch nicht alle Duplikate gefunden. Was ich am frustrierendsten finde, ist, dass es teilweise funktioniert hat und die Person denkt, es sei fast die richtige Abfrage… vielleicht würde es nur eine andere Variante bekommen…
Tatsächlich ist es unmöglich, mit dieser Art von Abfrage umzugehen einfache GROUP BY Abfrage. Warum ist das? Dies liegt daran, dass Sie beim Gruppieren nach einer Spalte die gleichen Werte der anderen Spalte auf mehrere Gruppen verteilen. Sie können dies visuell sehen, indem Sie nach diesen Spalten sortieren. Dies geschieht bei der Gruppierung. Ordnen Sie zunächst nach Spalte b und sehen Sie, wie sie gruppiert sind:
| a | b | c |
|---|---|---|
| 7 | 1 | 1 |
| 8 | 1 | 2 |
| 9 | 1 | 3 |
| 10 | 2 | 1 |
| 11 | 2 | 2 |
| 12 | 2 | 3 |
| 13 | 3 | 1 |
| 14 | 3 | 2 |
| 15 | 3 | 3 |
Wenn Sie nach Spalte b bestellen (gruppieren), werden die doppelten Werte in Spalte c sind in verschiedene Gruppen verteilt, sodass Sie sie nicht mit COUNT(DISTINCT c) zählen können, wie es die Person versucht hat. Aggregierte Funktionen wie COUNT() werden nur innerhalb einer Gruppe ausgeführt und haben keinen Zugriff auf Zeilen, die in anderen Gruppen platziert sind. Wenn Sie nach c bestellen, werden die doppelten Werte in Spalte b auf verschiedene Gruppen verteilt. Es ist nicht möglich, diese Abfrage so auszuführen, wie es gewünscht wird.
Einige korrekte Lösungen
Die wahrscheinlich einfachste Lösung besteht darin, die Duplikate für jede Spalte separat zu finden und UNION sie zusammen wie folgt:
Die Spalte what_col in der Ausgabe gibt an, in welcher Spalte der doppelte Wert gefunden wurde. Ein anderer Ansatz ist die Verwendung Unterabfragen:
Dies ist wahrscheinlich viel weniger effizient als der Ansatz UNION und zeigt jede duplizierte Zeile an, nicht nur die duplizierten Werte. Ein weiterer Ansatz besteht darin, Self-Joins für gruppierte Unterabfragen in der FROM -Klausel durchzuführen. Das korrekte Schreiben ist komplizierter, kann jedoch für einige komplexe Daten oder für die Effizienz erforderlich sein:
Jede dieser Abfragen reicht aus, und ich bin sicher, dass es auch andere Möglichkeiten gibt. Wenn Sie UNION verwenden können, ist dies wahrscheinlich die einfachste.
