mysql: Berechnung über mehrere Tabellen

[Johnny B.]

Hallo geehrtes Forum,

ich möchte gerne folgenden funktionierenden Query optimieren:

    SELECT                                              RgNr,  
            FORMAT(  
            ( SELECT SUM( preis ) FROM tour WHERE rechnung_id = rechnung.id ), 2)  
                                                        netto,  
            FORMAT(  
            ( SELECT SUM( preis ) +  SUM( ( mwst * preis/100 ) ) FROM tour WHERE rechnung_id = rechnung.id ), 2)  
                                                        brutto  
    FROM  rechnung  
    ORDER BY rgnr

Es gibt eine Tabelle 'rechnung' mit den Spalten id und rgnr.

Und es gibt eine Tabelle 'tour' mit den Spalten rechnung_id, preis und mwst.

Ausgeworfen werden sollen alle Rechnungen mit der berechneten Rechnungssumme netto und brutto. Einfache Sache eigentlich...

Diese zwei Subselects funktionieren, sind aber wahrscheinlich nicht optimal. Eleganter und performanter wäre wohl, ich würde die beiden Tabellen mit einem Join verknüpfen? Wenn ja, mit welchem? Irgendwie habe ich das Konzept der Joins immer noch nicht geblickt, trotz mehrmaligem Studieren der beiden Threads, auf die oft verwiesen wird. Mir schwirrt der Kopf von den vielen verschiedenen Möglichkeiten: es fällt mir schwer, gegebene Beispiele auf meinen Fall zu abstrahieren und durch Ausprobieren den richtigen Weg zu finden, gestaltet sich als schwierig - vielleicht mag jemand mir einen kurzen Wink in die richtige Richtung geben?

Verjointe INNEROUTERLEFTRIGHT Grüße
JOhnnY

[Vinzenz Mai]

Hallo,

ich möchte gerne folgenden funktionierenden Query optimieren:

ich verstehe Dich so: Du möchtest alternative Ansätze, die das gleiche (gewünschte und richtige) Ergebnis liefern vergleichen.

SELECT                                              RgNr,

FORMAT(
            ( SELECT SUM( preis ) FROM tour WHERE rechnung_id = rechnung.id ), 2)
                                                        netto,
            FORMAT(
            ( SELECT SUM( preis ) +  SUM( ( mwst * preis/100 ) ) FROM tour WHERE rechnung_id = rechnung.id ), 2)
                                                        brutto
    FROM  rechnung
    ORDER BY rgnr

  

> Es gibt eine Tabelle 'rechnung' mit den Spalten id und rgnr.  
>   
> Und es gibt eine Tabelle 'tour' mit den Spalten rechnung\_id, preis und mwst.  
  
und um eben nicht so abstrakt arbeiten zu müssen, um die Richtigkeit der Ergebnisse leicht einsehen zu können, ist es bei solchen Fragen sehr sinnvoll, einfach ein paar Beispieldatensätze anzugeben, zum Beispiel zwei, drei Rechnungen mit den zugehörigen Touren.  
  

> Diese zwei Subselects funktionieren, sind aber wahrscheinlich nicht optimal.  
  
Warum vermutest Du das?  
  

> Eleganter und performanter wäre wohl, ich würde die beiden Tabellen mit einem Join verknüpfen?  
  
Warum vermutest Du das? Eleganz ist das eine (und sehr vom Geschmack Abhängende), Performanz etwas anderes.  
  

> Wenn ja, mit welchem?  
  
Das ganze ist sehr einfach:  
Zu einer Rechnung kann es mehrere Touren geben. Eine Tour gehört zu genau einer Rechnung. Rechnungen ohne Touren interessieren nicht. Ich gehe weiter davon aus, dass eine Rechnungsnummer in der Tabelle Rechnungen nur einmal vorkommen kann, d.h. dass die Spalte rgnr über einen UNIQUE-Index verfügt.  
  
Somit bekommen wir eine Übersicht über die Rechnungen und die zugehörigen Touren über einen einfachen INNER JOIN:  
  
~~~sql
SELECT     -- gib mir  
    r.id,                 -- alle Spalten  
    r.rgnr,  
    t.rechnung_id,  
    t.preis,  
    t.mwst  
FROM                      -- aus der Tabelle  
    rechnung r            -- rechnung, über den Aliasnamen r angesprochen,  
INNER JOIN                -- die mit der Tabelle  
    touren t              -- Touren, über den Aliasnamen t angesprochen.  
ON                        -- über  
    r.id = t.rechnung_id  -- Gleichheit der Werte in den Spalten  
                          -- id bzw. rechnung_id verknüpft ist  

die ID-Spalten benötigen wir nicht, statt dessen noch eine berechnete Spalte mit dem Bruttopreis der Tour:

SELECT  
    r.rgnr,  
    t.preis netto,  
    t.preis + ((t.preis * t.mwst)/100) brutto  -- Berechneter Bruttopreis  
FROM  
    rechnung r  
INNER JOIN  
    touren t  
ON  
    r.id = t.rechnung_id  

Nun möchtest Du die Daten nach der Rechnungsnummer gruppieren, wobei Du als Aggregatsfunktion die Aufsummierung mit SUM() nutzen willst, weil Du die Gesamtnetto- und Gesamtbruttosumme haben willst:

SELECT  
    r.rgnr,  
    SUM(t.preis) netto,                            -- summiere in der Gruppe,  
    SUM(t.preis + ((t.preis * t.mwst)/100)) brutto -- hier auch  
FROM  
    rechnung r  
INNER JOIN  
    touren t  
ON  
    r.id = t.rechnung_id  
GROUP BY                              -- gruppiere  
    r.rgnr                            -- nach der Rechnungsnummer  

Auf die Sortierung kannst Du verzichten, weil MySQL bei Verwendung einer GROUP-BY-Klausel nach den Feldern der GROUP-BY-Klausel sortiert.

Die Formatierung einzubauen, solltest Du selbst hinbekommen.
Ach ja: Schau' Dir bitte die Einzelergebnisse, Schritt für Schritt an.

Die Performance zu messen, wäre der nächste Schritt. Dazu ist es erforderlich, dass in den beiden Tabellen genügend Datensätze vorhanden sind, d.h. mindestens die Anzahl der Datensätze, die nach zwei oder drei Jahren erwartet werden. Besser zehnmal so viele.

Freundliche Grüße

Vinzenz