SQL Erste Schritte

Tabellenausdrücke

Wenn Sie die Definition einer Sicht lediglich für eine einzige Abfrage benötigen, können Sie einen Tabellenausdruck verwenden.

Tabellenausdrücke sind temporäre Elemente und nur für die Dauer der SQL-Anweisung gültig. Anders als Sichten können sie nicht gemeinsam benutzt werden. Sie bieten jedoch im Vergleich zu Sichten eine größere Flexibilität.

Dieser Abschnitt beschreibt, wie allgemeine Tabellenausdrücke und verschachtelte Tabellenausdrücke in Abfragen eingesetzt werden.

Verschachtelte Tabellenausdrücke

Ein verschachtelter Tabellenausdruck ist eine temporäre Sicht, wobei die Definition in der Klausel FROM der Hauptabfrage verschachtelt (= direkt definiert) ist.

Die folgende Abfrage verwendet einen verschachtelten Tabellenausdruck, um den durchschnittlichen Gesamtverdienst, die Bildungsstufe und das Einstellungsjahr für Mitarbeiter mit einer höheren Bildungsstufe als 16 zu ermitteln:

 
     SELECT EDLEVEL, HIREYEAR, DECIMAL(AVG(TOTAL_PAY),7,2)
        FROM (SELECT EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,
                     SALARY+BONUS+COMM AS TOTAL_PAY
                 FROM EMPLOYEE
                 WHERE EDLEVEL > 16)  AS PAY_LEVEL
        GROUP BY EDLEVEL, HIREYEAR
        ORDER BY EDLEVEL, HIREYEAR	

Das Ergebnis lautet folgendermaßen:

     EDLEVEL HIREYEAR    3        
     ------- ----------- ---------
          17        1967  28850.00
          17        1973  23547.00
          17        1977  24430.00
          17        1979  25896.50
          18        1965  57970.00
          18        1968  32827.00
          18        1973  45350.00
          18        1976  31294.00
          19        1958  51120.00
          20        1975  42110.00

In dieser Abfrage wird zunächst das Einstellungsjahr mit einem verschachtelten Tabellenausdruck aus der Spalte HIREDATE extrahiert, damit es anschließend in der Klausel GROUP BY verwendet werden kann. In diesem Fall ist es unter Umständen sinnvoller, keine Sicht zu erstellen, wenn ähnliche Abfragen mit unterschiedlichen Werten für EDLEVEL ausgeführt werden sollen.

Das vorstehende Beispiel verwendet die integrierte Skalarfunktion DECIMAL. DECIMAL gibt die Dezimaldarstellung einer Zahl oder einer Zeichenfolge zurück. Ausführliche Informationen zu Funktionen finden Sie im Handbuch SQL Reference.

Allgemeine Tabellenausdrücke

Ein allgemeiner Tabellenausdruck ist ein Tabellenausdruck, der zur Verwendung in einer komplexen Abfrage erstellt wird. Er wird am Beginn der Abfrage in einer Klausel WITH definiert und benannt. Bei wiederholtem Bezug auf einen allgemeinen Tabellenausdruck wird dieselbe Ergebnismenge verwendet. Im Vergleich hierzu würde bei der Verwendung von verschachtelten Tabellenausdrücken oder Sichten die Ergebnismenge jedesmal erneut und möglicherweise mit unterschiedlichen Ergebnissen generiert.

Das folgende Beispiel listet alle Mitarbeiter im Unternehmen auf, die eine höhere Bildungsstufe als 16 aufweisen und deren Verdienst unter dem Durchschnittsverdienst der Mitarbeiter liegt, die in demselben Jahr eingestellt wurden und dieselbe Bildungsstufe haben. Die einzelnen Bestandteile der Abfrage werden im Anschluß an die Abfrage ausführlicher beschrieben.

(1)

     WITH
           PAYLEVEL AS                                                 
              (SELECT EMPNO, EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,           
                      SALARY+BONUS+COMM AS TOTAL_PAY                                     
                  FROM EMPLOYEE                                                         
                  WHERE EDLEVEL > 16),                                                       

(2)

           PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS         
              (SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY)                  
                  FROM PAYLEVEL                                            
                  GROUP BY EDLEVEL, HIREYEAR)                                                         

(3)

     SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, DECIMAL(AVG_TOTAL_PAY,7,2)  
        FROM PAYLEVEL, PAYBYED                                             
        WHERE EDLEVEL = EDUC_LEVEL                                        
          AND HIREYEAR = YEAR_OF_HIRE                              
          AND TOTAL_PAY < AVG_TOTAL_PAY                        

(1)
Dies ist ein allgemeiner Tabellenausdruck mit dem Namen PAYLEVEL. Die entsprechende Ergebnistabelle enthält eine Personalnummer, das Einstellungsjahr, den Gesamtverdienst des Mitarbeiters und seine bzw. ihre Bildungsstufe. In die Tabelle werden Zeilen nur für Mitarbeiter mit einer höheren Bildungsstufe als 16 aufgenommen.

(2)
Dies ist ein allgemeiner Tabellenausdruck namens PAYBYED (PAY BY EDucation = Gehalt nach Bildung). Er verwendet die Tabelle PAYLEVEL, die mit dem vorangegangenen allgemeinen Tabellenausdruck erstellt wurde. Er bestimmt die Bildungsstufe, das Einstellungsjahr und den Durchschnittsverdienst der Mitarbeiter innerhalb jeder Bildungsstufe, die in demselben Jahr eingestellt wurden. Die durch diese Tabelle zurückgegebenen Spalten wurden anders benannt (z. B. mit EDUC_LEVEL) als die Spaltennamen, die in der SELECT-Liste verwendet wurden. Die erzeugte Ergebnismenge namens PAYBYED ist mit dem Ergebnis identisch, das im Beispiel des Abschnitts "Verschachtelte Tabellenausdrücke" dargestellt ist.

(3)
Abschließend folgt die eigentliche Abfrage, die das gewünschte Ergebnis erzeugt. Die beiden Tabellen (PAYLEVEL, PAYBYED) werden verknüpft, um die Personen zu ermitteln, deren Gesamtverdienst geringer als der durchschnittliche Verdienst der Mitarbeiter ist, die in demselben Jahr eingestellt wurden. Bitte beachten Sie, daß die Tabelle PAYBYED auf der Tabelle PAYLEVEL basiert. Daher wird auf die Tabelle PAYLEVEL in der gesamten Anweisung tatsächlich zweimal zugegriffen. Bei beiden Zugriffen wird dieselbe Gruppe von Zeilen zur Auswertung der Abfrage verwendet.

Das Endergebnis lautet folgendermaßen:

     EMPNO  EDLEVEL YEAR_OF_HIRE TOTAL_PAY     5        
     ------ ------- ------------ ------------- ---------
     000210      17         1979      20132.00  25896.50
     


[ Seitenanfang | Vorherige Seite | Nächste Seite | Inhaltsverzeichnis | Index ]