Wählen Sie alle Zeilen aus, die doppelte Werte in einer von zwei Spalten innerhalb verschiedener Gruppen von Bezugsdatensätzen enthalten.

Question

Ich versuche, eine MySQL-Abfrage zu erstellen, die alle einzelnen Zeilen (nicht gruppiert) mit doppelten Werten aus einer Gruppe von Bezugsdatensätzen zurückgibt. Mit "Gruppen von Bezugsdatensätzen" meine ich solche mit derselben Kontonummer (siehe Beispiel unten).

Wählen Sie in jeder Gruppe von Bezugsdatensätzen, die dieselbe eindeutige Kontonummer haben, nur die Zeilen aus, deren Werte für das Feld date o amount Spalten mit den Werten einer anderen Zeile innerhalb der Datensatzgruppe dieses Kontos übereinstimmen. Werte sollten nur innerhalb der Kontogruppe als Duplikat betrachtet werden. Die Beispieltabelle und die idealen Ausgabedetails unten sollten die Dinge klären.

Außerdem bin ich nicht daran interessiert, dass Datensätze mit dem Status X zurückgegeben werden, selbst wenn sie doppelte Werte haben.

Kleine Beispieltabelle mit relevanten Daten:

id   account   invoice   date         amount   status
1    1         1         2012-04-01   0        X
2    1         2         2012-04-01   120      P
3    1         2         2012-05-01   120      U
4    1         3         2012-05-01   117      U
5    2         4         2012-04-01   82       X
6    2         4         2012-05-01   82       U
7    2         5         2012-03-01   81       P
8    2         6         2012-05-01   80       U
9    3         7         2012-03-01   80       P
10   3         8         2012-04-01   79       U
11   3         9         2012-04-01   78       U

Ideale Ausgabe, die von der gewünschten SQL-Abfrage zurückgegeben wird:

id   account   invoice   date         amount   status
2    1         2         2012-04-01   120      P
3    1         2         2012-05-01   120      U
4    1         3         2012-05-01   117      U
6    2         4         2012-05-01   82       U
8    2         6         2012-05-01   80       U
10   3         8         2012-04-01   79       U
11   3         9         2012-04-01   78       U

Daher sollten die Zeilen 7/9 und 8/9 nicht beide zurückgegeben werden, da ihre doppelten Werte im Rahmen ihrer jeweiligen Konten nicht als doppelt angesehen werden. Zeile 8 sollte jedoch zurückgegeben werden, da sie einen doppelten Wert mit Zeile 6 hat.

Später möchte ich vielleicht die Auswahl weiter verfeinern, indem ich nur doppelte Zeilen mit übereinstimmenden Status abfrage, so dass Zeile 2 ausgeschlossen würde, weil sie nicht mit den anderen beiden in der Datensatzgruppe dieses Kontos übereinstimmt. Wie viel schwieriger würde das die Abfrage machen? Müsste nur eine WHERE- oder HAVING-Klausel hinzugefügt werden, oder ist es komplizierter als das?

Ich hoffe, dass meine Erklärung dessen, was ich zu erreichen versuche, sinnvoll ist. Ich habe versucht, INNER JOIN zu verwenden, aber das gibt jede gewünschte Zeile mehr als einmal zurück. Ich möchte keine Duplikate von Duplikaten.

Tabellenstruktur und Stichprobenwerte:

CREATE TABLE payment (
  id int(11) NOT NULL auto_increment,
  account int(10) NOT NULL default '0',
  invoice int(10) NOT NULL default '0',
  date date NOT NULL default '0000-00-00',
  amount int(10) NOT NULL default '0',
  status char(1) NOT NULL default '',
  PRIMARY KEY  (id)
);

INSERT INTO payment VALUES (1, 1, 1, '2012-04-01', 0, 'X'); 
INSERT INTO payment VALUES (2, 1, 2, '2012-04-01', 120, 'P'); 
INSERT INTO payment VALUES (3, 1, 2, '2012-05-01', 120, 'U'); 
INSERT INTO payment VALUES (4, 1, 3, '2012-05-01', 117, 'U'); 
INSERT INTO payment VALUES (5, 2, 4, '2012-04-01', 82, 'X'); 
INSERT INTO payment VALUES (6, 2, 4, '2012-05-01', 82, 'U'); 
INSERT INTO payment VALUES (7, 2, 5, '2012-03-01', 81, 'p'); 
INSERT INTO payment VALUES (8, 2, 6, '2012-05-01', 80, 'U'); 
INSERT INTO payment VALUES (9, 3, 7, '2012-03-01', 80, 'U'); 
INSERT INTO payment VALUES (10, 3, 8, '2012-04-01', 79, 'U'); 
INSERT INTO payment VALUES (11, 3, 9, '2012-04-01', 78, 'U');

Answer 1

Diese Art der Abfrage kann als Halbanschluss .

Semijoins werden verwendet, um Zeilen aus eine der Tabellen im Join.

Zum Beispiel:

select distinct l.*
from payment l
inner join payment r
on 
  l.id != r.id and l.account = r.account and
  (l.date = r.date or l.amount = r.amount)
where l.status != 'X' and r.status != 'X'
order by l.id asc;

Beachten Sie die Verwendung von distinct und dass ich nur Spalten aus der linken Tabelle auswähle. Dadurch wird sichergestellt, dass es keine Duplikate gibt.

Die Join-Bedingung prüft dies:

• nicht eine Zeile mit sich selbst verbindet ( l.id != r.id )
• Zeilen auf demselben Konto sind ( l.account = r.account )
• und entweder das Datum oder der Betrag derselbe ist ( l.date = r.date or l.amount = r.amount )

---

Für den zweiten Teil Ihrer Frage müssten Sie die on Klausel in der Abfrage.

Answer 2

Dies scheint zu funktionieren

select * from payment p1
join payment p2 on
(p1.id != p2.id 
 and p1.status != 'X'
 and p1.account = p2.account
 and (p1.amount = p2.amount or p1.date = p2.date))
group by p1.id

http://sqlfiddle.com/#!2/a50e9/3