Wie können Sie die Zeilen mit gleichen Spalten finden?

Question

Wenn ich eine Tabelle mit 2 wichtigen Spalten habe,

CREATE TABLE foo (id INT, a INT, b INT, KEY a, KEY b);

Wie kann ich alle Zeilen finden, die sowohl a y b die in beiden Zeilen gleich sind? Zum Beispiel, in diesem Datensatz

id | a | b
----------
1  | 1 | 2
2  | 5 | 42
3  | 1 | 42
4  | 1 | 2 
5  | 1 | 2
6  | 1 | 42

Ich möchte alle Zeilen mit Ausnahme der folgenden zurückbekommen id=2 da sie einzigartig ist in (a,b) . Im Grunde möchte ich alle verletzenden Zeilen finden, die eine

ALTER TABLE foo ADD UNIQUE (a, b);

Etwas besseres als eine n^2 for-Schleife wäre schön, da meine Tabelle 10M Zeilen hat.

Für Bonuspunkte : Wie entferne ich alle Zeilen bis auf eine (es ist mir egal, welche, Hauptsache eine bleibt übrig)?

Answer 1

Hier ein anderer Ansatz

select \* from foo f1 where exists(
  select \* from foo f2 where
    f1.id != f2.id and
    f1.a = f2.a and
    f1.b = f2.b )

wie auch immer, auch wenn ich finde es ein bisschen mehr lesbar, wenn Sie eine so große Tabelle haben, sollten Sie den Ausführungsplan überprüfen, Unterabfragen haben einen schlechten Ruf in Bezug auf Leistung...

Sie sollten auch in Erwägung ziehen, den Index zu erstellen (natürlich ohne die Unique-Klausel), um die Abfrage zu beschleunigen... bei umfangreichen Operationen ist es manchmal besser, die Zeit für die Erstellung des Indexes zu verwenden, die Aktualisierung durchzuführen und dann den Index zu löschen... in diesem Fall sollte ein Index auf (a, b) sicherlich eine große Hilfe sein...

Answer 2

Ihr erklärtes Ziel ist die Entfernung aller doppelten Kombinationen von (a,b) . Hierfür können Sie ein Mehrtabellen-DELETE verwenden:

DELETE t1
  FROM foo t1
  JOIN foo t2 USING (a, b)
 WHERE t2.id > t1.id

Bevor Sie es ausführen, können Sie prüfen, welche Zeilen entfernt mit:

SELECT DISTINCT t1.id
  FROM foo t1
  JOIN foo t2 USING (a, b)
 WHERE t2.id > t1.id

Die WHERE-Klausel ist t2.id > t1.id werden alle bis auf diejenige mit dem höchsten Wert für id . In Ihrem Fall werden nur die Zeilen mit id gleich 2, 5 oder 6 bleiben würde.

Answer 3

Wenn der id-Wert im Endprodukt überhaupt keine Rolle spielt, d. h. wenn Sie alle neu nummerieren könnten und es in Ordnung wäre, und wenn id eine serielle Spalte ist, dann wählen Sie einfach "select distinct" für die beiden Spalten in einer neuen Tabelle, löschen Sie alle Daten aus der alten Tabelle und kopieren Sie dann die temporären Werte wieder hinein.