[ MySQL ] - Bestellungen addieren und Rechnungen ausgeben (JOIN)?

[Tobias Menzel]

Hi,

Folgendes Szenario: Ich habe in einem Shop die Tabellen "kunden" und "bestellungen". Bei jeder neuen Bestellung wird in letzterer ein Datensatz u.a. mit der Bestellsumme und der Kunden-ID abgelegt. Nun soll nicht bei jeder Bestellung eine Rechnung ausgestellt werden, sondern erst wenn die Summe getätigter Bestellungen einen bestimmten Betrag übersteigt.

Ich möchte also (mit möglichst wenigen Abfragen) die Kunden (und die dazugehörigen Bestellungen) erhalten, deren Bestellungen einen Maximalbetrag überschreiten. In den Rechnungen sollen auch alle enthaltenen Bestellungen aufgelistet werden.

Da ich (zugegebenermaßen) das Prinzip von JOIN und Subselects noch nicht ganz verstanden habe, gehe ich (in PHP) recht umständlich so vor:

1. SELECT kunde FROM bestellungen WHERE status=0 GROUP BY kunde
   ( "status" ist ein Flag: 0=Rechnung noch nicht ausgestellt )
   
   
2. Für alle auf diese Weise erhaltenen Kunden:
   SELECT * FROM bestellungen WHERE kunde=KUNDE AND status=0
   
   
3. Alle auf diese Weise erhaltenen Bestellungen summiere ich, und liste sie auf, wenn der Betrag meinen Zielbetrag übersteigt. (der Status aller Rechnungen wird in einer weiteren Abfrage auf 1 (ausgestellt) gesetzt.

Dies ist natürlich nicht besonders performant. Ich würde mich daher freuen, wenn mir jemand einen Denkanstoß für eine Optimierung geben könnte.

Grüße,

Tobi

 

[marbe]

Hallo,

wo ist denn der Unterschied zwischen den Abfragen 1 und 2?

Hier 1 und 2 als Subselect (oft etwas langsamer)

SELECT * FROM bestellungen 
WHERE kunde IN (SELECT DISTINCT kunde FROM bestellungen WHERE status=0)

Zum Optimieren würde ich Dir noch empfehlen Index anlegen auf kunde und status.
Das beschleunigt die Abfragen unter umständen enorm.

CREATE INDEX ix_kunde ON bestellungen (kunde);
CREATE INDEX ix_status ON bestellungen (status);

Mehr dazu hier!

 

[Tobias Menzel]

Hi,

erst einmal Danke für Deine Antwort.

wo ist denn der Unterschied zwischen den Abfragen 1 und 2?

Mit der ersten Abfrage bekomme ich alle Kunden, für die überhaupt nicht ausgestellte Rechnungen existieren. Das Ergebnis der Abfrage gehe ich dann in einer Schleife (hier per mysql_fetch_assoc() in PHP) durch. Für jeden der Kunden im Ergebnis kenne ich nun ja seine ID (Primärschlüssel) und kann die zweite Abfrage starten, die mir alle nicht ausgestellten Rechnungen für den jeweiligen Kunden liefert.

Die Optimierung per Index werde ich ausprobieren. Allerdings sehe ich den Flaschenhals bei mir eher in den nötigen Schleifen als in den Abfragen selber.

Was ich bräuchte wäre eine Abfrage, die mir alle Kunden liefert, deren offene Bestellungen eine Summe von mindestens Betrag X ergeben: Auf meine Weise erhalte ich ja zunächst alle Kunden, die überhaupt offene Bestellungen haben. Ich würde das gerne so eingrenzen, dass in der Abfrage die Bestellungen jedes Kunden schon addiert werden (Spalte "preis") - also (in Pseudocode) etwas wie dieses:

SELECT * FROM kunden WHERE SUM(preis der bestellungen des Kunden mit status 0) > X

Gruß
.

 

[marbe]

Hallo Tobias,

ich dachte das mit der Summe ergibt sich dann. Dein Problem kannst Du in einer Abfrage klären. Wurde nicht in mehren Abfragen die Daten in den Speicher laden.

Die modifizierte Abfrage:

SELECT bestellungen.kunde, SUM(bestellungen.betrag) SUMME FROM bestellungen 
WHERE status=0
GROUP BY bestellungen.kunde
HAVING SUM(bestellungen.betrag) > X

Erklärung:
Der rote Teil schränkt die Menge auf die Fälle ein die status = 0 haben. Join ist wie es aussieht nicht erforderlich. Ist ja nur die Tabelle "bestellungen". Hab ich erst auf dem zweiten Blick gesehen.
Bei SUM() wird die Summe der Beträge gerechnet und im HAVING wird der Grenzwert eingeschränkt. Du kannst Du X testen wie es sich verhält.



Du musst nur noch deine Spalten ersetzen (es sei denn deine Spalte heisst wirklich betrag)

 

[Tobias Menzel]

Hi,

danke, dies sollte für diesen Zweck funktionieren.

Allerdings merke ich grade, dass ich mich mit meiner Problembeschreibung doch etwas ins Knie geschossen habe. Tatsächlich ist in "bestellungen" nämlich nicht der Preis direkt notiert, sondern nur die ID einer Produktgruppe. Auch in der Tabelle "produktgruppe" steht noch nicht der Preis, sondern lediglich die ID einer Preiskategorie (z.B. 1, 2 oder 3). Erst in der Tabelle "preiskategorie" ist der tatsächliche Betrag hinterlegt. :-(

Tabelle "bestellungen":
| ID | KUNDE | PRODUKT |
|  1 |     1 |       2 |
|  2 |     1 |       3 |

Tabelle "produkt":
| ID | NAME | PREISKATEGORIE |
|  1 | Haus |              1 |
|  2 | Baum |              2 |
|  3 | Meer |              1 |

Tabelle "preiskategorie":
| ID | PREIS |
|  1 |    20 |
|  2 |    15 |

Der Kunde mit der ID "1" hat hier also insgesamt 35 € zu bezahlen.

Das ganze Ding müsste daher wohl leider doch über 3 Tabellen gehen.

Gibt es für diese Struktur auch eine Lösung?

Gruß
.

 

[marbe]

Hallo Tobias,

einfach joinen

SELECT bestellungen.kunde, SUM(preiskategorie.PREIS) SUMME 
FROM bestellungen INNER JOIN produkt
ON bestellungen.produkt=produkt.id
INNER JOIN preiskategorie
ON produkt.PREISKATEGORIE=preiskategorie.id
WHERE status=0
GROUP BY bestellungen.kunde
HAVING SUM(preiskategorie.PREIS)  > X

Das sollte nun dein Ergebnis liefern.

 

[Tobias Menzel]

Danke.

Ich muss mir das Prinzip von JOIN wirklich mal zu eigen machen - bislang hatte ich irgendwie keine Referenz gefunden, die mir die Logik dahinter schlüssig erklärt ...

Gruß
.