Nützliche Erweiterungen

Aus Wikibooks
Zur Navigation springen Zur Suche springen

Seitentitel: Einführung in SQL: Nützliche Erweiterungen
(Einführung in SQL: Nützliche Erweiterungen)

In diesem Kapitel werden verschiedene Erweiterungen des SELECT-Befehls genauer behandelt.

Die Beispiele beziehen sich auch hier auf den Anfangsbestand der Beispieldatenbank; auf die Ausgabe der selektierten Datensätze wird wiederum weitgehend verzichtet. Bitte probieren Sie alle Beispiele aus und nehmen Sie verschiedene Änderungen vor, um die Auswirkungen zu erkennen.

DISTINCT – keine doppelten Einträge[Bearbeiten]

Wenn Sie den DISTINCT-Parameter bei einem SELECT-Befehl angeben, erhalten Sie nur eindeutige Ergebnisse:

Liste der Fahrzeuge, für die Schadensfälle aufgetreten sind
SELECT distinct Fahrzeug_ID
  from Zuordnung_SF_FZ;
Crystal Clear app terminal.png Ausgabe
Fahrzeug_ID
      3
      4
      5
      6
      7

Bitte beachten Sie: Als „eindeutig“ gilt immer die gesamte Zeile, also alle Spalten zusammen. Die folgende Abfrage liefert alle Datensätze; Fahrzeuge mit mehreren Schadensfällen stehen auch mehrfach in der Liste.

Red x.svg Nur theoretisch DISTINCT, praktisch nicht
SELECT distinct Fahrzeug_ID, ID
  from Zuordnung_SF_FZ;

Als Gegenstück gibt es den ALL-Parameter, der ausdrücklich alle Datensätze abfragt. Da dies der Standardwert ist, wird er äußerst selten benutzt:

SELECT all Fahrzeug_ID
  from Zuordnung_SF_FZ

Beschränkung auf eine Anzahl Zeilen[Bearbeiten]

Häufig will man nicht sofort das gesamte Ergebnis sehen, sondern nur einen Teil der Zeilen.

Vor allem im Netzwerk kostet es seine Zeit, eine größere Menge von Datensätzen zu übertragen. Es ist deshalb oft praktisch, zunächst einen Teil des Ergebnisses zu holen und anzuzeigen. Während der Anwender sich mit diesem Teilergebnis beschäftigt, wird „im Hintergrund“ der nächste Abschnitt geholt usw.

Im SQL-Standard gibt es dafür (noch) kein Verfahren. Abweichend vom üblichen Vorgehen in diesem Buch erhalten Sie Lösungen für verschiedene DBMS.

Anstelle konstanter Werte (ohne Klammern) kann in allen folgenden Fällen auch ein SQL-Ausdruck (in Klammern) angegeben werden.

Firebird: FIRST SKIP oder ROWS[Bearbeiten]

Möglichkeiten bei Firebird

Firebird bietet gleich zwei Lösungen an, die erste mit FIRST / SKIP:

 SELECT [DISTINCT]
   [ FIRST <value1> ]
   [  SKIP <value2> ]
   <select list> FROM ... /* usw. */

Der FIRST-Parameter gibt an, wie viele Zeilen am Anfang anzuzeigen sind; der SKIP-Parameter legt fest, wie viele Zeilen davor übersprungen werden sollen. Beide Parameter werden einzeln oder zusammen benutzt; sie folgen direkt als erste Klausel nach DISTINCT, noch vor der Spaltenliste. Einige Beispiele:

➤ Nur FIRST zeigt die ersten Zeilen.

Firebird-Version
select FIRST 10
       ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name;

➤ Nur SKIP überspringt die ersten Zeilen.

Firebird-Version
select SKIP 10
       ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name;

➤ FIRST zeigt die ersten 10 Zeilen an, aber wegen SKIP werden vorher 5 Zeilen übersprungen.

Firebird-Version
select FIRST 10 SKIP 5
       ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name;

➤ Mit einem Ausdruck kann dafür gesorgt werden, dass etwa das erste Viertel der Datensätze abgerufen wird:

Firebird-Version
select FIRST ( (SELECT count(*) from Mitarbeiter) / 4 )
       ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name;
Der Wert für FIRST wird aus der Anzahl der Datensätze berechnet.

Die zweite Firebird-Variante benutzt mit ROWS direkt Zeilennummern:

 SELECT ... FROM ... WHERE ...
  ORDER BY ...
   ROWS <value1> [ TO <value2> ]

Die ROWS-Parameter legen fest, dass (nur) eine bestimmte Anzahl Zeilen angezeigt werden sollen, die durch die Zeilennummern gekennzeichnet sind.

  • Wenn nur ROWS benutzt wird, bezeichnet <value1> die Gesamtzahl der angezeigten Zeilen.
  • Wenn ROWS zusammen mit TO benutzt wird, ist <value1> die erste Zeilennummer und <value2> die letzte Zeilennummer.

Einige Beispiele:

➤ Ausgabe der Zeilen 10 bis 20 (also insgesamt 11 Zeilen)

Firebird-Version
select ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name
  ROWS 10 TO 20;

➤ Der erste Datensatz gemäß Sortierung

Firebird-Version
select ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name
  ROWS 1;

➤ Der letzte Datensatz gemäß Sortierung

Firebird-Version
select ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name desc
  ROWS 1;

Vor allem das letzte Beispiel, bei dem mit DESC die Sortierung umgekehrt wird, ist oft sehr nützlich.

Bei einer Interbase-Datenbank sind auch prozentuale Angaben möglich; das ist bei Firebird entfallen.

Microsoft SQL: TOP[Bearbeiten]

Möglichkeiten bei Microsoft-SQL

Der MS-SQL Server benutzt diese Syntax:

 SELECT [DISTINCT]
   TOP ( <value> ) [PERCENT] [WITH TIES]
   <select list> FROM ... /* usw. */

Mit den TOP-Parametern wird festgelegt, dass (nur) eine bestimmte Anzahl von Zeilen angezeigt werden sollen, die durch die Zeilennummern gekennzeichnet sind. Diese Parameter folgen direkt als erste Klausel nach DISTINCT, noch vor der Spaltenliste.

  • <value> bezeichnet die Gesamtzahl der angezeigten Zeilen. Es wird empfohlen, die Klammern immer zu setzen.
  • Wenn TOP zusammen mit PERCENT benutzt wird, handelt es sich dabei um die prozentuale Angabe der Zeilenzahl.
  • WITH TIES muss zusammen mit ORDER BY benutzt werden und liefert dann zusätzliche doppelte Zeilen, wenn der letzte Wert nach der Reihenfolge gleich ist den Werten in danach folgenden Zeilen.

Einige Beispiele:

➤ Nur TOP zeigt die ersten Zeilen.

Version für MS-SQL
select
   TOP 10
       ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name;

➤ TOP + PERCENT zeigt z. B. das erste Viertel an.

Version für MS-SQL
select
   TOP 25 PERCENT
       ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name;

➤ OFFSET NEXT für einen Teil der Daten (Paging), z.B. Zeile 11-20

Version für MS-SQL 2012
select
       ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name
 OFFSET 10 ROWS
 FETCH NEXT 10 ROWS ONLY;;

➤ Der letzte Datensatz gemäß Sortierung

Version für MS-SQL
select 
   TOP 1
       ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name desc;

Vor allem das letzte Beispiel, bei dem mit DESC die Sortierung umgekehrt wird, ist oft sehr nützlich.

MySQL und PostgreSQL: LIMIT[Bearbeiten]

Möglichkeiten bei MySQL und PostgreSQL

Diese DBMS benutzen den LIMIT-Parameter. Dieser Parameter folgt nach ORDER BY, wobei die Sortierung nicht angegeben werden muss.

 SELECT ... FROM ... WHERE ...
  ORDER BY ...
  LIMIT <value1> OFFSET <value2>

Dabei wird mit <value1> angegeben, wie viele Zeilen am Anfang angezeigt werden sollen. Mit <value2> kann nach dem Begriff OFFSET außerdem angegeben werden, wie viele Zeilen davor übersprungen werden sollen.

➤ Es werden die ersten 10 Zeilen angezeigt.

MySQL-Version
select ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name
 LIMIT 10;

➤ Es werden die ersten 10 Zeilen angezeigt, aber vorher werden 5 Zeilen übersprungen.

MySQL-Version
select ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name
 LIMIT 10 OFFSET 5;

➤ Der letzte Datensatz gemäß Sortierung

MySQL-Version
select ID, Name, Vorname, Abteilung_ID as Abt
  from Mitarbeiter
 order BY Name desc
 LIMIT 1;

Vor allem das letzte Beispiel, bei dem mit DESC die Sortierung umgekehrt wird, ist oft sehr nützlich.

Eine andere Schreibweise für diesen Parameter verzichtet auf das Wort OFFSET:

 SELECT ... FROM ... WHERE
  ORDER BY ...
  LIMIT [ <value2>, ] <value1>

Bei dieser Variante wird genauso mit <value1> angegeben, wie viele Zeilen am Anfang angezeigt werden sollen. Mit <value2> kann außerdem angegeben werden, wie viele Zeilen davor übersprungen werden sollen; dieser Wert wird jedoch zuerst angegeben und durch ein Komma von der gewünschten Zeilenzahl getrennt.

Die Bedeutung beider Varianten ist identisch, sodass Beispiele nicht nötig sind. Es ist wohl Geschmackssache, welche Version „eingängiger“ ist.

Oracle: ROWNUM[Bearbeiten]

Möglichkeiten bei Oracle

Bei dem DBMS Oracle gibt es bei jedem SELECT-Ergebnis eine implizite Spalte Rownum. Man kann diese Spalte mit ausgeben lassen. Solange man kein ORDER BY angibt, ist die Reihenfolge der ausgegebenen Sätze nicht festgelegt. Dieselbe Abfrage kann an einem anderen Tag durchaus eine andere Nummerierung der Sätze hervorbringen. Das liegt z. B. daran, dass jemand die Datensätze in der Zwischenzeit reorganisiert hat.

Oracle-Version
SELECT Name, rownum 
  FROM Mitarbeiter;
Crystal Clear app terminal.png Ausgabe
NAME     ROWNUM
-------  ------
Müller      1
Schneider   2
Meyer       3
Schmitz     4   /* usw. */
Rownum als implizite Spalte der Ergebnismenge

Wenn man diese Spalte Rownum nicht angibt, dann wird sie auch nicht ausgegeben. Ihr Vorteil ist, dass man sie auch bei WHERE verwenden kann:

Oracle-Version
SELECT Name
  FROM Mitarbeiter 
 WHERE Rownum <= 2;
Crystal Clear app terminal.png Ausgabe
NAME     ROWNUM
-------  ------
Müller      1
Schneider   2
Es werden nur die ersten 2 Zeilen angezeigt.

Folgende Formulierung funktioniert allerdings nicht, wenn man nur den 10. Satz ausgeben will:

Red x.svg Oracle-Version
SELECT Name
  FROM Mitarbeiter; 
 WHERE Rownum = 10;
Nur den 10. Satz anzeigen – so geht es nicht.

Das liegt daran, dass der Zähler Rownum nur die Zeilen zählt, die auch wirklich ausgegeben werden. Wenn die Tabelle 500 Sätze hat, dann wird bei jedem Satz geprüft, ob Rownum bereits den Wert 10 erreicht hat. Das ist jedoch nie der Fall, da der Zähler immer den Wert 0 behält.

Da muss man schon das DBMS zwingen, die Ergebnismenge mit der Rownum zwischenzuspeichern. Dann geht es:

Check-green.svg Oracle-Version
SELECT Name
  FROM ( SELECT Name, rownum R
         FROM Mitarbeiter )
 WHERE R = 10;
Nur den 10. Satz anzeigen – so klappt es.

Welcher Satz dabei ausgegeben wird, ist jedoch dem Zufall überlassen.

Wenn man die Ergebnismenge sortiert ausgeben will und dabei nur die ersten 6 Sätze ausgegeben haben will, dann funktioniert die folgende Formulierung nicht:

Red x.svg Oracle-Version
SELECT Name
  FROM Mitarbeiter; 
 WHERE Rownum <= 6
 ORDER BY Name;
Die alphabetisch ersten 6 Namen ausgeben – so geht es nicht.

Man wird feststellen, dass zwar nur 6 Sätze ausgegeben werden, dass das aber keinesfalls immer die alphabetisch ersten 6 Namen sind. Das liegt daran, dass zunächst WHERE ausgewertet wird und danach erst sortiert wird. Es werden also – wie bei den vorangegangenen Beispielen beschrieben – beliebige 6 Sätze gelesen, und nur diese 6 Sätze werden sortiert.

Für die richtige Lösung muss die Datenbank schon etwas mehr tun. Sie muss zuerst alle vorhandenen Sätze sortieren und dann die ersten 6 Sätze ausgeben:

Check-green.svg Oracle-Version
SELECT Name
  FROM ( SELECT Name
           FROM Mitarbeiter 
          ORDER BY Name )
 WHERE Rownum <= 6;
Die alphabetisch ersten 6 Namen ausgeben – so klappt es.

Sybase: ROWCOUNT[Bearbeiten]

Möglichkeiten bei Sybase

Bei diesem DBMS wird zuerst die Anzahl der gewünschten Zeilen angegeben, danach folgt der SELECT-Befehl.

Sybase-Version
SET rowcount 10;
SELECT Name, rownum 
  FROM Mitarbeiter;
Rowcount als Vorgabe

Mehrere Abfragen zusammenfassen[Bearbeiten]

UNION – Vereinigung[Bearbeiten]

Mit der UNION-Klausel werden mehrere Abfragen verknüpft und als einheitliche Ergebnismenge geliefert. Dieses Verfahren ist Standard und steht in jedem DBMS zur Verfügung. Es ist vor allem in zwei Situationen sinnvoll:

  • Die Daten stammen aus verschiedenen Tabellen mit ähnlicher Struktur und sollen gemeinsam angezeigt werden.
  • Die Daten stammen aus derselben Tabelle; die Auswahlbedingungen sind so komplex, dass eine einzelne Abfrage nicht möglich, nicht sinnvoll oder zu unübersichtlich wäre.

Die Syntax einer solchen Verknüpfung sieht aus wie folgt, wobei auch mehr als zwei Abfragen verknüpft werden können:

 SELECT <Spaltenliste1> 
   FROM <Tabellenliste1> 
  WHERE <Bedingungen1>
        UNION [ DISTINCT | ALL ]
 SELECT <Spaltenliste2> 
   FROM <Tabellenliste2> 
  WHERE <Bedingungen2>

Bei den einzelnen Abfragen können grundsätzlich alle Klauseln benutzt werden. Bitte beachten Sie folgende Bedingungen:

  • Alle Einzelabfragen müssen in der Anzahl und Reihenfolge der Ergebnis-Spalten übereinstimmen. Die Datentypen müssen je nach DBMS und Version genau gleich sein oder zumindest so ähnlich, dass sie automatisch konvertiert werden können.
  • Die Spaltennamen werden aus der ersten Spaltenliste übernommen, ggf. unter Berücksichtigung von Alias-Namen.
  • Grundsätzlich werden doppelte Zeilen aus den Einzelabfragen unterdrückt, d. h. DISTINCT ist Standard und kann weggelassen werden. Wenn doppelte Zeilen angezeigt werden sollen, ist ALL anzugeben.
  • Die Benutzung von Klammern sowie ORDER BY für das Gesamtergebnis wird je nach DBMS unterschiedlich geregelt.
Aufgabe

Das folgende Beispiel ist eine einfache Zusammenfassung aller Fahrzeuge aus den Tabellen Fahrzeuge und Dienstwagen:

select ID, Kennzeichen, Farbe
  from Dienstwagen
       union
select ID, Kennzeichen, Farbe
  from Fahrzeug;

Im folgenden Beispiel werden als Spalte Var Konstanten eingetragen, die die Herkunft der Daten angeben, und es werden verschiedene Auswahlbedingungen benutzt.

select 'D' as Var, ID, Kennzeichen, Farbe
  from Dienstwagen
 where Fahrzeugtyp_ID <= 3
       union
select 'F', ID, Kennzeichen, Farbe
  from Fahrzeug
 where Kennzeichen LIKE 'B%';

Wenn die Ergebnismenge sortiert werden soll, werden je nach DBMS unterschiedliche Schreibweisen benötigt.

MySQL-Version mit Klammern um die Einzelabfragen herum, ORDER BY wird zusätzlich angegeben.
( select 'D' as Var, ID, Kennzeichen, Farbe
    from Dienstwagen
   where Fahrzeugtyp_ID <= 3 )
union
( select 'F', ID, Kennzeichen, Farbe
    from Fahrzeug
   where Fahrzeugtyp_ID <= 3 )
order by Kennzeichen;
Firebird-Version mit einer Hauptabfrage mit ORDER BY und der UNION-Verknüpfung als Unterabfrage.
select * from
( select 'D' as Var, ID, Kennzeichen, Farbe
    from Dienstwagen
   where Fahrzeugtyp_ID <= 3
         union
  select 'F', ID, Kennzeichen, Farbe
    from Fahrzeug
   where Fahrzeugtyp_ID <= 3 )
order by Kennzeichen;

Mit der folgenden Abfrage werden verschiedene Listen von Mitarbeitern zusammengefasst: zum einen diejenigen, die im September Geburtstag haben, zum anderen die Leiter mit einer Mobil-Rufnummer:

select Personalnummer, Name, Vorname, Geburtsdatum, Ist_Leiter as Leiter, Mobil
  from Mitarbeiter
 where EXTRACT(month from Geburtsdatum) = 9
       union
select Personalnummer, Name, Vorname, Geburtsdatum, Ist_Leiter, Mobil
  from Mitarbeiter
 where Ist_Leiter = 'J' and Mobil <> '';

Offensichtlich kann diese Abfrage auch ohne UNION erreicht werden, nämlich durch: WHERE <bedingung1> OR ( <bedingung2> AND <bedingung3> ). Sie können sich aber sicherlich noch viel kompliziertere Situationen vorstellen, vor allem wenn die WHERE-Bedingungen auf Informationen in unterschiedlichen weiteren Tabellen zugreifen müssen.

Andere Varianten[Bearbeiten]

Unter den gleichen Voraussetzungen wie UNION gibt es weitere Wege, um Einzelabfragen zusammenzufassen. Diese stehen aber nur vereinzelt zur Verfügung; wir beschränken uns deshalb auf Hinweise.

Mit INTERSECT (MS-SQL) erhält man den Durchschnitt der Teilabfragen.

 SELECT <Spaltenliste1>   FROM <Tabellenliste1>   WHERE <Bedingungen1>
        INTERSECT
 SELECT <Spaltenliste2>   FROM <Tabellenliste2>   WHERE <Bedingungen2>

Die Ergebnismenge besteht aus allen Zeilen, die sowohl zum ersten SELECT-Befehl als auch zum zweiten SELECT-Befehl gehören.

Mit EXCEPT (MS-SQL) oder MINUS (Oracle) wird die Differenz der Teilabfragen zurückgegeben.

 SELECT <Spaltenliste1>   FROM <Tabellenliste1>   WHERE <Bedingungen1>
        EXCEPT | MINUS
 SELECT <Spaltenliste2>   FROM <Tabellenliste2>   WHERE <Bedingungen2>

Die Ergebnismenge besteht aus allen Zeilen, die zum ersten SELECT-Befehl gehören, aber beim zweiten SELECT-Befehl nicht enthalten sind.

Wie bei UNION können auch solche Verknüpfungen durch sehr komplizierte JOINs und WHERE-Klauseln nachgebildet werden.

CASE WHEN – Fallunterscheidungen[Bearbeiten]

Immer wieder möchte man innerhalb eines SQL-Befehls je nach Situation unterschiedliche Werte erhalten. Das einfachste Beispiel ist die Tabelle Mitarbeiter: Anstelle des Feldinhalts Ist_Leiter mit 'J' oder 'N' kann 'Leiter' oder eine leere Zeichenkette angezeigt werden. Dafür ist der CASE-Ausdruck vorgesehen, den es in zwei Varianten gibt.

Als <expression> können wahlweise konstante Werte oder komplexe Ausdrücke verwendet werden. Der CASE-Ausdruck ist nicht nur für SELECT, sondern auch für Speichern-Befehle geeignet und nicht nur (wie in den meisten Beispielen) als Teil der Spaltenliste, sondern auch in der WHERE-Klausel oder an anderen Stellen, an denen entsprechende Werte benötigt werden.

Der Wert, den der CASE-Ausdruck zurückgibt, erhält einen passenden Datentyp, der sich aus den Result-Werten ergibt.

Simple Case – die einfache Fallunterscheidung[Bearbeiten]

Die einfache Variante hat folgende Syntax:

 CASE <expression>
      WHEN <expression1> THEN <result1>
    [ WHEN <expression2> THEN <result2> ]  /* usw. */
    [ ELSE <default> ]
 END

Bei dieser Version wird der Wert, der sich durch den ersten Ausdruck beim CASE ergibt, nacheinander mit den Ausdrücken nach WHEN verglichen. Sobald eine Gleichheit festgestellt wird, wird der Wert, der sich durch den Result-Ausdruck ergibt, als Wert für den CASE-Ausdruck übernommen. Wenn es keine Übereinstimmung gibt, wird der Wert des Default-Ausdrucks übernommen; wenn ELSE nicht vorhanden ist, wird NULL als Wert genommen. Beispiele:

Aufgabe

Ein vorhandener Feldinhalt, nämlich 'J' oder 'N', wird für die Ausgabe durch andere Texte ersetzt:

select Personalnummer as Pers, Name, Vorname, Geburtsdatum as Geb,
       CASE Ist_Leiter
            WHEN 'J' THEN 'Leiter'
            ELSE ''
       END as Leiter, 
       Mobil
  from Mitarbeiter;
Crystal Clear app terminal.png Ausgabe
PERS       NAME          VORNAME    GEB         LEITER  MOBIL
---------  ------------  ---------  ----------  ------  -----
40001      Langmann      Matthias   28.03.1976  Leiter
40002      Peters        Michael    15.11.1973
50001      Pohl          Helmut     27.10.1980  Leiter  (0171) 4123456
50002      Braun         Christian  05.09.1966          (0170) 8351647

Dieselbe Lösung ohne ELSE-Zweig ändert nur die Anzeige:

select Personalnummer as Pers, Name, Vorname, Geburtsdatum as Geb,
       CASE Ist_Leiter
            WHEN 'J' THEN 'Leiter'
       END as Leiter, 
       Mobil
  from Mitarbeiter;
Crystal Clear app terminal.png Ausgabe
PERS       NAME          VORNAME    GEB         LEITER  MOBIL
---------  ------------  ---------  ----------  ------  -----
40001      Langmann      Matthias   28.03.1976  Leiter
40002      Peters        Michael    15.11.1973  <null>
50001      Pohl          Helmut     27.10.1980  Leiter  (0171) 4123456
50002      Braun         Christian  05.09.1966  <null>  (0170) 8351647
Aufgabe

Bei der Art der Versicherungsverträge möchten wir die Varianten im „Klartext“ lesen:

select Vertragsnummer as Vertrag, Abschlussdatum as Datum,
       CASE Art
            WHEN 'VK' THEN 'Vollkasko'
            WHEN 'TK' THEN 'Teilkasko'
            WHEN 'HP' THEN 'Haftpflicht'
       END
  from Versicherungsvertrag;
Crystal Clear app terminal.png Ausgabe
VERTRAG  DATUM       CASE
-------  ----------  -----------
DB-04    25.01.2008  Haftpflicht
RH-01    11.12.1976  Vollkasko
RD-02    29.01.1988  Haftpflicht
RM-03    13.01.1996  Haftpflicht
RD-04    23.11.2006  Haftpflicht
RR-05    29.06.1990  Teilkasko

Searched Case – die komplexe Fallunterscheidung[Bearbeiten]

Die komplexe Variante hat keinen Ausdruck hinter dem CASE; sie arbeitet nach folgender Syntax:

CASE 
     WHEN <bedingung1> THEN <result1>
   [ WHEN <bedingung2> THEN <result2> ]  /* usw. */
   [ ELSE <default> ]
END

Bei dieser Variante werden nacheinander Bedingungen geprüft. Sobald eine Bedingung als WAHR festgestellt wird, wird der Wert, der sich durch den Result-Ausdruck ergibt, als Wert für den CASE-Ausdruck übernommen. Wenn es keine Übereinstimmung gibt, wird der Wert des Default-Ausdrucks übernommen; wenn ELSE nicht vorhanden ist, wird NULL als Wert genommen. Beispiele:

Aufgabe

In einer einzigen Zeile wird angegeben, wieviele Versicherungsverträge innerhalb eines Jahrzehnts abgeschlossen wurden.

select
    SUM( CASE WHEN EXTRACT(YEAR from Abschlussdatum) between 1970 and 1979 
              THEN 1 
              ELSE 0 
         END ) as S_197_,
    SUM( CASE WHEN EXTRACT(YEAR from Abschlussdatum) between 1980 and 1989 
              THEN 1 
              ELSE 0 
         END ) as S_198_,
    SUM( CASE WHEN EXTRACT(YEAR from Abschlussdatum) between 1990 and 1999 
              THEN 1 
              ELSE 0 
         END ) as S_199_,
    SUM( CASE WHEN EXTRACT(YEAR from Abschlussdatum) >= 2000               
              THEN 1 
              ELSE 0 
         END ) as S_200_
  from Versicherungsvertrag;
Crystal Clear app terminal.png Ausgabe
S_197_   S_198_   S_199_  S_200_
   6        6        6       5

Dazu wird für jedes Jahrzehnt eine Spalte vorgesehen. Jede Spalte enthält einen CASE-Ausdruck mit einer WHEN-Bedingung. Wenn für eine Zeile diese Bedingung TRUE ergibt, wird 1 zur Summe dieses Jahrzehnts addiert, andernfalls 0.

Aufgabe

Der CASE-Ausdruck soll innerhalb von ORDER BY eine unterschiedliche

Sortierung, abhängig von der Art der Versicherung, erreichen:

select ID, Art,
       Abschlussdatum as Datum, Vertragsnummer as Vertr,
       Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
  from Versicherungsvertrag
  order by Art,
           CASE WHEN Art = 'TK' THEN ID
                WHEN Art = 'VK' THEN Mitarbeiter_ID
                WHEN Art = 'HP' THEN Fahrzeug_ID
           END;
Crystal Clear app terminal.png Ausgabe
ID  ART  DATUM       VERTR  MIT  FZG
------------------------------------
14  HP   15.03.1998  KG-03    9   16
18  HP   17.05.2000  HG-03    9   17
19  HP   21.09.2004  HB-04    9   19
10  HP   23.11.2006  RD-04    9   20
 6  HP   25.01.2008  DB-04    9   21
15  HP   27.03.1988  KV-04   10   22
11  TK   29.06.1990  RR-05    9   23
12  TK   14.02.1978  KB-01   10    6
21  VK   20.06.1982  XH-02    9    8
23  VK   19.07.2002  XO-04    9   18
 7  VK   11.12.1976  RH-01   10    5
22  VK   05.06.1992  XW-03   10   13

Hier wurden alle Abschnitte numerisch sortiert. Auch die Teilsortierung nach Datum funktioniert, muss aber unter Umständen überarbeitet werden:

Red x.svg Firebird-Version
select ID, Art,
       Abschlussdatum as Datum, Vertragsnummer as Vertr,
       Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
  from Versicherungsvertrag
  order by Art,
           CASE WHEN Art = 'TK' THEN ID
                WHEN Art = 'VK' THEN Mitarbeiter_ID
                WHEN Art = 'HP' THEN Abschlussdatum
           END;
Crystal Clear app terminal.png Ausgabe
Invalid token.
SQL error code = -104.
Datatypes are not comparable in expression CASE.

Auch wenn Firebird (wie im ersten Beispiel) Namen von Spalten akzeptiert, werden die Datentypen der Spalten vergleichen. Diese sind offensichtlich nicht kompatibel; also bricht Firebird diese Abfrage ab. Aber in der ORDER BY-Klausel können auch die Nummern der Spalten angegeben werden; dann klappt es:

Check-green.svg Firebird-Version
select ID, Art,
       Abschlussdatum as Datum, Vertragsnummer as Vertr,
       Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
  from Versicherungsvertrag
  order by Art,
           CASE WHEN Art = 'TK' THEN ID
                WHEN Art = 'VK' THEN Mitarbeiter_ID
                WHEN Art = 'HP' THEN 2
           END;
Mit Nummern der Spalten anstelle der Namen.

Bitte wundern Sie sich nicht über das identische Ergebnis wie oben bei der Sortierung nach Fahrzeug_ID: Die Fahrzeuge wurden in der gleichen Reihenfolge erfasst wie die Verträge; also stimmen in der ersten Zeit beide Sortierungen überein.

Die Sortierung kann nur einheitlich festgelegt werden, aber nicht mal so, mal so:

Red x.svg Art der Sortierung kann nicht gemischt werden.
select ID, Art,
       Abschlussdatum as Datum, Vertragsnummer as Vertr,
       Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
  from Versicherungsvertrag
  order by Art,
           CASE
              WHEN Art = 'TK' THEN ID             ASC
              WHEN Art = 'VK' THEN Mitarbeiter_ID ASC
              WHEN Art = 'HP' THEN 2              DESC
           END;
Crystal Clear app terminal.png Ausgabe
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 7, column 39. ASC.
Check-green.svg Einheitliche Sortierung DESC sollte möglich sein
select ID, Art,
       Abschlussdatum as Datum, Vertragsnummer as Vertr,
       Mitarbeiter_ID as Mit, Fahrzeug_ID as FZG
  from Versicherungsvertrag
  order by Art,
           CASE
              WHEN Art = 'TK' THEN ID             
              WHEN Art = 'VK' THEN Mitarbeiter_ID 
              WHEN Art = 'HP' THEN 2              
           END DESC;

Zumindest unter Firebird gelingt dennoch keine korrekte Sortierung: nur der Abschnitt 'TK' wird absteigend sortiert, die beiden anderen nicht. Sie müssen aber sowieso selbst ausprobieren, was in welchem DBMS möglich ist und was nicht.

CASE-Ausdruck beim Speichern[Bearbeiten]

Wie beim SELECT-Befehl kann ein CASE-Ausdruck auch beim Speichern an verschiedenen Stellen benutzt werden, vor allem bei den Zuweisungen der Werte für INSERT und UPDATE sowie bei der WHERE-Klausel für UPDATE und DELETE. Da sich diese Kapitel vor allem mit Abfragen beschäftigen, beschränken wir uns auf ein Beispiel.

Aufgabe

Die Zuständigkeit der Mitarbeiter für die Versicherungsverträge wird neu geregelt:

  • Vollkasko-Verträge gehören zum Aufgabenbereich des Abteilungsleiters (Mitarbeiter-ID 9).
  • Teilkasko-Verträge gehören zum Aufgabenbereich des Mitarbeiters mit ID 12.
  • Die Haftpflicht-Verträge werden in Abhängigkeit von der ID aufgeteilt auf die Mitarbeiter 10 und 11.
update Versicherungsvertrag
   set Mitarbeiter_id =
       CASE WHEN Art = 'VK' THEN  9
            WHEN Art = 'TK' THEN 12
            WHEN Art = 'HP' THEN 10 + MOD(ID, 2)
       END;

Es wird also eine normale SET-Anweisung geschrieben. Die CASE-Anweisung liefert die benötigten Werte, wobei für den Fall „Haftpflicht“ für gerade IDs der Wert (10+0) und für ungerade IDs der Wert (10+1) gesetzt wird. Eine WHERE-Klausel ist nicht erforderlich, weil alle Verträge neu zugeordnet werden sollen.

Zusammenfassung[Bearbeiten]

In diesem Kapitel lernten Sie einige nützliche Erweiterungen vor allem für Abfragen kennen:

  • Auf den DISTINCT-Parameter zur Auswahl wird genauer eingegangen.
  • Je nach DBMS wird unterschiedlich geregelt, wenn nur eine gewisse Anzahl von Zeilen gewünscht wird.
  • Mit UNION wird das Ergebnis von zwei oder mehr Abfragen in einer gemeinsamen Ergebnistabelle zusammengefasst.
  • Mit CASE WHEN sind Fallunterscheidungen für gewisse Werte möglich; dies ist auch beim Speichern von Datensätzen sowohl für die Werte als auch für die WHERE-Klausel hilfreich.

Übungen[Bearbeiten]

Übung 1 Richtig oder falsch? Zur Lösung

Welche der folgenden Aussagen sind richtig, welche sind falsch?

  1. Es ist Standard, den Parameter ALL bei einem SELECT-Befehl anzugeben.
  2. Bei SELECT DISTINCT werden nur solche Zeilen angezeigt, die sich in mindestens einer Spalte unterscheiden.
  3. Es ist äußerst selten erforderlich, das Ergebnis einer Abfrage nur in Teilen zu holen.
  4. Für die Anzeige oder Verwendung einer ROW_NUMBER hat der SQL-Standard (2003) ein Verfahren vorgeschrieben.
  5. Ein solches Verfahren wird von den meisten DBMS verwendet, ist aber in unterschiedlicher Weise verwirklicht.
  6. Bei UNION, INTERSECT usw. müssen die ausgewählten Spalten von der Anzahl her übereinstimmen.
  7. Bei UNION, INTERSECT usw. müssen die ausgewählten Spalten vom Datentyp her genau übereinstimmen.

Übung 2 DISTINCT und ALL Zur Lösung

Bitte überprüfen Sie die folgenden Befehle:

--  Variante 1
select distinct COUNT(*)
  from Mitarbeiter
 group by Abteilung_ID

--  Variante 2
select all COUNT(*)
  from Mitarbeiter
 group by Abteilung_ID

Worin unterscheidet sich die Ausgabe? Welche wichtige Information fehlt vor allem bei Variante 2?

Übung 3 UNION, INTERSECT usw. Zur Lösung

Was ist an den folgenden Befehlen falsch oder fragwürdig? Wie kann man das DBMS dazu bringen, den „fragwürdigen“ Befehl auf jeden Fall auszuführen?

--  Befehl 1
select Name, Vorname                   from Mitarbeiter
       union
select Name, Vorname, Geburtsdatum     from Versicherungsnehmer

--  Befehl 2
select Name, Vorname, Abteilung_ID     from Mitarbeiter
       union
select Name, Vorname, Geburtsdatum     from Versicherungsnehmer

Übung 4 UNION, INTERSECT usw. Zur Lösung

Erstellen Sie eine Abfrage für die Tabellen Versicherungsvertrag, Fahrzeug, Zuordnung_SF-FZ, in der folgende Bedingungen berücksichtigt werden:

  • Es sollen Vertragsnummer, Abschlussdatum, Kennzeichen sowie ggf. anteilige Schadenshöhe angezeigt werden.
  • Fahrzeuge mit einem Schaden sollen vollständig angezeigt werden.
  • Fahrzeuge ohne Schaden sollen nur angezeigt werden, wenn der Vertrag vor 1990 abgeschlossen wurde.
  • Das Ergebnis soll nach Schadenshöhe und Kennzeichen sortiert werden.

Benutzen Sie UNION zur Verknüpfung der Verträge mit und ohne Schaden.

Übung 5 Fallunterscheidung für Nachschlagewerte Zur Lösung

Schreiben Sie einen SELECT-Befehl, bei dem die korrekte Briefanrede für die Mitarbeiter erstellt wird.

Hinweis: Benutzen Sie CONCAT zum Verknüpfen mehrerer Zeichenketten.

Übung 6 Fallunterscheidung für Bereiche Zur Lösung

Zur Tabelle Fahrzeug soll aus dem Kennzeichen die regionale Herkunft abgeleitet und angezeigt werden. Schreiben Sie eine Abfrage für diese Spalten Kennzeichen und Herkunft.

Hinweis: Benutzen Sie POSITION zur Feststellung des Bindestrichs sowie SUBSTRING.

Zusatzfrage: Wie müsste ein solches Problem sinnvollerweise gelöst werden, falls eine solche Zuordnung häufiger und allgemeiner benötigt wird?

Übung 7 Fallunterscheidung für mehrere Varianten Zur Lösung

Aus der Tabelle Versicherungsnehmer sollen Name, Vorname und Anschrift angezeigt werden. Außerdem soll jede Adresse eine Markierung bekommen: F = (eigene) Firmenkunden, P = (eigene) Privatkunden, X = eXterne Verträge (d. h. Kunden fremder Versicherungsgesellschaften). Begründen Sie die Reihenfolge der WHEN-ELSE-Bedingungen.

Übung 8 Fallunterscheidung beim Speichern Zur Lösung

Schreiben Sie einen UPDATE-Befehl, der nach Ablauf eines Versicherungsjahres den Prämiensatz für das nächste Jahr ändert. Berücksichtigen Sie dabei folgende Bedingungen:

  • Neue Verträge, für die noch kein Prämiensatz gilt, werden auf 200 [Prozent] gesetzt.
  • Verträge mit einem Prämiensatz von mindestens 100 werden um 20 reduziert.
  • Verträge mit einem Prämiensatz von weniger als 100 werden um 10 reduziert.
  • Der Mindestsatz von 30 darf nicht unterschritten werden.

Ignorieren Sie dabei zunächst, dass dies nur im Fall von Schadensfreiheit gelten darf und innerhalb eines Jahres nur einmal neu berechnet werden darf.

Übung 9 Fallunterscheidung beim Speichern Zur Lösung

Bei genauerer Untersuchung von Übung 8 sind weitere Bedingungen im UPDATE-Befehl erforderlich.

  1. In welcher Weise könnte die Schadensfreiheit berücksichtigt werden? Abhängig von der Höhe des Schadens soll sich der Prämiensatz gar nicht, wenig oder mehr erhöhen.
  2. Wie kann unter Verwendung des Datums Prämienänderung gesichert werden, dass die Neuberechnung nur einmal jährlich stattfinden darf?

Versuchen Sie, dies im selben UPDATE-Befehl zu berücksichtigen. Sie sollen keinen Befehl schreiben, sondern die notwendigen Klauseln erwähnen und erläutern.

Lösungen[Bearbeiten]

Lösung zu Übung 1 Richtig oder falsch? Zur Übung

Die Aussagen 2, 5, 6 sind richtig. Die Aussagen 1, 3, 4, 7 sind falsch.

Lösung zu Übung 2 DISTINCT und ALL Zur Übung

Variante 1 nennt jede Anzahl von Mitarbeitern pro Abteilung genau einmal. Bei Variante 2 gibt es diese Anzeige für jede Abteilung einzeln. Dabei fehlt vor allem die Angabe der Abteilung_ID, ohne die die Ausgabe ziemlich sinnlos ist.

Lösung zu Übung 3 UNION, INTERSECT usw. Zur Übung

Bei Befehl 1 werden unterschiedlich viele Spalten ausgewählt, das ist unzulässig.

Bei Befehl 2 unterscheiden sich die Datentypen der dritten Spalte; es ist unsicher, ob das DBMS die unterschiedlichen Spalten einheitlich als Zeichenkette (zulässig) oder nicht-kompatibel (unzulässig) interpretieren will.

Ein CAST beider Spalten auf VARCHAR macht die Datentypen kompatibel.

Lösung zu Übung 4 UNION, INTERSECT usw. Zur Übung

Eine mögliche Variante lautet so (Firebird-Version für ORDER BY):

select * from (
  select Vertragsnummer, Abschlussdatum, Kennzeichen, Schadenshoehe
    from Versicherungsvertrag vv
         inner join Fahrzeug        fz on fz.ID = vv.Fahrzeug_ID
         right join Zuordnung_SF_FZ zu on fz.ID = zu.Fahrzeug_ID
union
  select Vertragsnummer, Abschlussdatum, Kennzeichen, 0
    from Versicherungsvertrag vv
         inner join Fahrzeug        fz on fz.ID = vv.Fahrzeug_ID
   where Abschlussdatum < '01.01.1990' and not
      EXISTS ( select ID from Zuordnung_SF_FZ zu where zu.Fahrzeug_ID = fz.ID )
) order by Schadenshoehe, Kennzeichen;

Der zweite SELECT kann auch so geschrieben werden:

  select Vertragsnummer, Abschlussdatum, Kennzeichen, 0
    from Versicherungsvertrag vv
         inner join Fahrzeug        fz on fz.ID = vv.Fahrzeug_ID
         left  join Zuordnung_SF_FZ zu on fz.ID = zu.Fahrzeug_ID
   where Abschlussdatum < '01.01.1990' and zu.ID is null

Lösung zu Übung 5 Fallunterscheidung für Nachschlagewerte Zur Übung
select CONCAT( 'Sehr geehrte',
                CASE Geschlecht
                     WHEN 'M' THEN 'r Herr '
                     WHEN 'W' THEN ' Frau '
                     ELSE '/r Frau/Herr '
                END,
                Name ) as Anrede
  from Mitarbeiter;

Hinweise: CONCAT oder die String-Verknüpfung erzeugen u. U. eine falsche Länge des ersten Teils. Das kann mit TRIM „repariert“ werden; dann ist aber das abschließende Leerzeichen hinzuzufügen. Der ELSE-Zweig ist überflüssig, weil Geschlecht nicht NULL sein kann; er wird nur der Vollständigkeit halber genannt.

Lösung zu Übung 6 Fallunterscheidung für Bereiche Zur Übung
select Kennzeichen,
       CASE SUBSTRING(Kennzeichen from 1 for POSITION('-', Kennzeichen)-1 )
            WHEN 'RE'  THEN 'Kreis Recklinghausen'
            WHEN 'GE'  THEN 'Stadt Gelsenkirchen'
            WHEN 'E'   THEN 'Stadt Essen'
            WHEN 'BO'  THEN 'Stadt Bochum'
            WHEN 'HER' THEN 'Stadt Herne'
            ELSE 'unbekannt'
       END as Herkunft
  from Fahrzeug
 order by 2, 1;

Zur Zusatzfrage: Eine Tabelle Region o. ä. würde mit LEFT JOIN über den o. g. SUBSTRING verknüpft. Auch ein INNER JOIN wäre möglich, dann würden aber Fahrzeuge fehlen, deren Kennzeichen in der Tabelle Region fehlen.

Lösung zu Übung 7 Fallunterscheidung für mehrere Varianten Zur Übung
select Name, Vorname, PLZ, Strasse, Hausnummer,
       CASE
            WHEN Eigener_Kunde = 'N'                       THEN 'X'
            WHEN Vorname is null or Fuehrerschein is null  THEN 'F'
            ELSE 'P'
       END as Markierung
  from Versicherungsnehmer;

Zur Reihenfolge: Bei den Fremdverträgen wird nicht zwischen Firmen- und Privatkunden unterschieden; dies muss also zuerst geprüft werden. Bei Firmenkunden sind weder Vorname noch Führerschein-Datum registriert, das wird als nächstes geprüft (eines der WHEN-Kriterien würde genügen). Alle übrigen Fälle sind Privatkunden.

Lösung zu Übung 8 Fallunterscheidung beim Speichern Zur Übung
update Versicherungsvertrag
   set Praemiensatz = CASE
                          WHEN Praemiensatz is null THEN 200
                          WHEN Praemiensatz >=  100 THEN Praemiensatz - 20
                          WHEN Praemiensatz >=   40 THEN Praemiensatz - 10
                          ELSE 30
                      END
 where -- passende Bedingungen

Lösung zu Übung 9 Fallunterscheidung beim Speichern Zur Übung
  1. Es wird eine geschachtelte CASE-Anweisung verwendet. Zunächst wird unterschieden, ob überhaupt Schadensfälle aufgetreten sind. Im ersten Zweig „kein Schaden“ wird die Lösung von Übung 7 benutzt. Im zweiten Zweig „mit Schaden“ wird eine ähnliche CASE-Anweisung eingefügt, die abhängig von der Schadenshöhe den Prämiensatz erhöht.
  2. In der WHERE-Klausel wird geprüft, ob die letzte Prämienänderung überschritten ist. Gleichzeitig mit der Neuberechnung wird dieses Datum um ein Jahr weitergesetzt.

Hinweis: Dieses Verfahren wird ausführlich im Kapitel Prozeduren: Automatisches UPDATE behandelt. Bei Interesse können Sie im dortigen Code den abschließenden UPDATE-Befehl vergleichen.