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

select * from foo where a = b

Oder übersehe ich etwas?

\===

Aktualisierung zur Verdeutlichung:

select * from 
foo as a
inner join foo as b
on a.a = b.a AND b.a = b.b
and a.id != b.id

++++++++++ Nach 3. Klarheit bearbeiten:

select f1.id
FROM foo as f1
INNER JOIN foo as f2
ON f1.a = f2.a AND f1.b=f2.b AND f1.id != f2.id

Aber ich bin angeschossen, also überprüfen Sie es selbst.

Answer 2

Sollte das nicht funktionieren?

SELECT * FROM foo WHERE a = b

\=== Bearbeiten ===

das Wie über

SELECT a, b FROM foo GROUP BY a, b HAVING COUNT(*) > 1

\=== Letzte Überarbeitung, bevor ich diese Frage aufgebe ===

SELECT foo.* FROM foo, (
   SELECT a, b FROM foo GROUP BY a, b HAVING COUNT(*) > 1
) foo2
WHERE foo.a = foo2.a AND foo.b = foo2.b

Answer 3

SELECT * 
FROM foo first
JOIN foo second
  ON ( first.a = second.a
       AND first.b = second.b ) 
  AND (first.id <> second.id )

Es sollten alle Zeilen angezeigt werden, in denen mehr als eine Zeile die gleiche Kombination von a und b aufweist.

Hoffen Sie einfach, dass Sie einen Index für die Spalten a und b haben.

Answer 4

Könnten Sie bitte klarstellen, was Sie letztendlich tun müssen? Die beste Lösung kann davon abhängen (wollen Sie z. B. einfach alle Zeilen mit doppelten Schlüsseln löschen?)

Eine Möglichkeit ist, diese Tabelle zu handhaben (ich bin nicht sicher, ob mySQL das unterstützt, sie ist von SYBASE), wenn Sie nur eindeutige Schlüsselzeilen wollen:

SELECT MIN(id), A, B FROM FOO GROUP BY A, B HAVING COUNT(*)>1

Ihre genaue Frage (auch wenn mir nicht ganz klar ist, warum Sie alle Zeilen außer id=2 benötigen) lautet:

SELECT F1.*  
FROM FOO F1 , 
     (SELECT A, B FROM FOO GROUP BY A, B HAVING COUNT(*)>1) F2
WHERE F1.A=F2.A and F1.B=F2.B

Um alle Duplikate zu löschen, können Sie zum Beispiel Folgendes tun

DELETE FOO WHERE NOT EXISTS
(SELECT 1 from
    (SELECT MIN(id) 'min_id' FROM FOO GROUP BY A, B HAVING COUNT(*)>1) UINIQUE_IDS 
 WHERE id = min_id)

Als Alternative können Sie

  SELECT MIN(id) 'id', A, B INTO TEMPDB..NEW_TABLE 
  FROM FOO GROUP BY A, B HAVING COUNT(*)>1

  TRUNCATE TABLE FOO
  // Drop indices on FOO
  INSERT FOO SELECT * FROM NEW_TABLE
  // Recreate indices on FOO

Answer 5

Versuchen Sie dies:

    With s as (Select a,b from foo group by a,b having Count(1)>1)
Select foo.* from foo,s where foo.a=s.a and foo.b=s.b

Diese Abfrage sollte doppelte Zeilen in der Tabelle foo anzeigen.