Wikibooks

Programmieren mit dBASE PLUS: SQL-Datenbanken: Einführung in die SQL-Syntax


Umgang mit Feldern, Feldlisten, Feldnamen, Tabellennamen

Bearbeiten

Umgang mit Feldern, Duplikate entfernen

Bearbeiten

Alle Adressen zeigen: 

SELECT * FROM ADRESSEN

Einschränkung auf bestimmte Felder: 

SELECT ID, VORNAME, FIRMA1, FIRMA2, STRASSE, PLZ, ORT FROM ADRESSEN

Verkettung von Feldern mit ||. 

SELECT FIRMA1||', '||VORNAME AS NAME FROM ADRESSEN

Hinweis: Bei Feldern mit NULL-Werten wird in Firebird ein leerer Wert zurückgegeben, bei dbf-Tabellen nicht.

Mit DISTINCT werden alle Duplikate entfernt. 

SELECT DISTINCT ORT FROM ADRESSEN

Hinweis: Alle doppelten Werte werden nicht angezeigt. Also werden Berlin und Hamburg nur jeweils einmal angezeigt, obwohl sie öfter in der Tabelle vorkommen.

DISTINCT bezieht sich immer auf die Übereinstimmung aller Felder einer Abfrage. Durch das Hinzufügen der weiteren Felder werden auch mehr Sätze angezeigt. Ausgefiltert wird nur, wenn alle drei Felder die gleichen Werte haben! 

SELECT DISTINCT FIRMA1, PLZ, ORT FROM ADRESSEN

Maskieren von Feldnamen mit " Durch die Maskierung können Leerzeichen als Feld- und Aliasname verwendet werden. Außerdem können dadurch reservierte Worte der Datenbank als Feld- und Aliasname verwendet werden. (z.B. DATE - Siehe Anhang: Reservierte Wörter") !!! NUR FIREBIRD !!! 

SELECT FIRMA1 AS "Firmen Name", ANLAGEDATUM AS "DATE" FROM ADRESSEN

Alias für Tabellen und Felder

Bearbeiten

Für jede Tabelle und für jedes Feld kann ein Aliasname angegeben werden. Bei Feldern wird der Aliasname dann als Feldname in der Ergebnisliste angezeigt. Tabellen haben automatisch den Tabellennamen als Aliasnamen und Felder können über diesen Aliasnamen referenziert werden (ADRESSE.ID). Bei Mehrfachverwendung der selben Tabellen ist es aber notwendig, jeder Tabelle einen eigenen Aliasnamen zu geben. Indem kurze Namen verwendet werden (z.B. nur einen oder zwei Buchstaben) kann eine Menge Schreibarbeit gespart werden und umfangreiche Abfragen werden übersichtlicher. Wenn mehrere Tabellen verwendet werden, muss vor jedem Feldnamen der Aliasnamen der Tabelle stehen. Dadurch wird sichergestellt, dass die richtigen Felder angesprochen werden (z.B. bei JOINS). Ein Alias kann entweder direkt mit einem Leerzeichen getrennt hinter die Feldbeschreibung oder Tabelle geschrieben werden oder in der Form: AS ALIASNAME.


Anzeigen aller Adressen mit Bankverbindung. Feld Firma1 wird als Name und BLZ als Bankleitzahl angezeigt. 

SELECT A.FIRMA1 AS NAME, B.BLZ AS BANKLEITZAHL FROM ADRESSEN A JOIN ADRESSENBANK B ON (B.NR = A.ID)

Mit einem Tabellenalias und dem * können von einer bestimmten Tabelle alle Felder angezeigt werden. Mit einem Komma getrennt, können dann einzelne weitere Felder angegeben werden. 

SELECT A.ID AS NUMMER, A.* FROM ADRESSEN A

Umwandlungs-Funktionen in Abfragen

Bearbeiten

Der SQL-Standard bietet einige rudimentäre Funktionen zur Umwandlung von Daten und Datentypen.


SUBSTRING() UPPER(), LOWER()

Bearbeiten

Extrahieren eines Teilstrings aus Stringfeldern. Nur die ersten 5 Zeichen des Feldes FIRMA1 anzeigen 

SELECT SUBSTRING(FIRMA1 FROM 1 FOR 5) AS NAME FROM ADRESSEN

Mit UPPER() werden Feldwerte in Großbuchstaben umgewandelt. Bei dbf-Tabellen geht auch LOWER() zur Umwandlung in Kleinbuchstaben. 

SELECT UPPER(FIRMA1) AS GROSS FROM ADRESSEN

CAST() Umwandlung von Datentypen

Bearbeiten

Umwandeln eines Datums in einen String 

SELECT CAST(ANLAGEDATUM AS CHARACTER(10)) AS DATUM FROM ADRESSEN

Hinweis: Hier ist zu beachten, dass Firebird ein anderes Datumsformat zurückliefert (JJJJ-MM-TT) als dbf-Tabellen (TT.MM.JJJJ).

Umwandeln einer Zahl in einen String 

SELECT GESAMT AS ZAHL, CAST(GESAMT AS CHARACTER(10)) AS ZEICHEN FROM RECHNUNG

Hinweis: Bei dbf-Tabellen wird als Dezimalstelle ein Komma verwendet, bei Firebird ein Punkt.

Umwandeln eines String in eine Zahl 

SELECT BLZ, CAST(BLZ AS NUMERIC(10)) AS BLZNUMMER FROM ADRESSENBANK

Umwandeln einer Ganzzahl in eine Dezimalzahl mit zwei Stellen. 

SELECT ID, CAST(ID AS NUMERIC(10,2)) AS NUMMER FROM ADRESSEN

EXTRACT() Extrahieren von Daten aus Datumsfeldern

Bearbeiten

Jahr, Monat und Tag aus Datum extrahieren 

SELECT EXTRACT(YEAR FROM ANLAGEDATUM) AS JAHR,
EXTRACT(MONTH FROM ANLAGEDATUM) AS MONAT,
EXTRACT(DAY FROM ANLAGEDATUM) AS TAG 
FROM ADRESSEN

Hinweis: Auch hier liefert Firebird (Ganzzahl) ein anderes Format zurück als dbf-Tabellen (Dezimalzahl).

Berechnungen, SUB-Selects

Bearbeiten

Der SQL-Standard hält einige Funktionen zum Zählen und Berechnen von Werten bereit.


Einfache Berechnungen: + - * /

Bearbeiten

Netto und MwSt.-Betrag addieren: 

SELECT NETTO+MWSTBETRAG AS BRUTTO FROM RECHNUNG

Berechnet werden 16% vom Gesamtbetrag. Angezeigt werden beide Felder: 

SELECT GESAMT, (GESAMT*16)/100 AS MWST FROM RECHNUNG

Berechnet werden 16% vom Gesamtbetrag und Gesamt + 16% , angezeigt werden Netto, MwSt. und Brutto: 

SELECT GESAMT AS NETTO , (GESAMT*16)/100 AS MWST,  GESAMT +((GESAMT*16)/100) AS BRUTTO FROM RECHNUNG

Aggregatfunktionen SUM(), COUNT(), MIN(), MAX(), AVG():

Bearbeiten

Alle Aggregatfunktionen können mit einem Feldnamen, der Option ALL <Feldname> oder DISTINCT <Feldname> ausgeführt werden. Die Angabe eines Feldnamens oder ALL berücksichtigt alle Sätze, die nicht NULL sind. DISTINCT berücksichtigt doppelte Werte jeweils nur einmal. Bei SUM(), AVG(), MIN() und MAX() muss immer ein Feldname angegeben werden, bei COUNT kann auch ein * in der Klammer stehen, dann wird unabhängig von einem Feld gezählt.


Summe Gesamt aller Rechnungen 

SELECT SUM(GESAMT) AS GESAMT FROM RECHNUNG

Anzahl aller Einträge in Adressen, mit der Angabe eines Feldes und DISTINCT. 

SELECT COUNT(*) AS ANZAHL_GESAMT, COUNT(VORNAME) AS ANZAHL_EXISTIERENDE_VORNAMEN, COUNT(ALL VORNAME) AS MIT_ALL, COUNT(DISTINCT VORNAME) AS VERSCHIEDENE_VORNAMEN FROM ADRESSEN

Durchschnittsbetrag aller Rechnungen und kleinste und größte ID. 

SELECT AVG(GESAMT) AS DURCHSCHNITT,  MIN(GESAMT) AS KLEINSTER_BETRAG, MAX(GESAMT) AS GROESSTER_BETRAG FROM RECHNUNG

Feldwerte durch SUBSELECT ermitteln

Bearbeiten

Mit einem sog. SUBSELECT kann eine eigene SELECT-ABFRAGE als Ergebnis für einen Feldwert benutzt werden. Ein SUBSELECT liefert üblicherweise einen Satz und ein Feld zurück. Für jeden Datensatz wird ein SUBSELECT ausgeführt, so dass die Abfrage bei großen Tabellen lange dauern kann. In den meisten Fällen ist das Ergebnis eines SUBSELECTS auch mit einem JOIN zu erreichen, was aus Performancegründen in jedem Fall vorzuziehen ist. Bei Verwendung von dbf-Tabellen ist der Performanceunterschied allerdings nicht so groß. Bei dbf-Tabellen funktioniert die Referenzierung über den Feldnamen im Zusammenhang mit Aggregatfunktionen leider nicht. Hier ist offensichtlich ein Fehler in der Umsetzung des SQL-Befehls vorhanden. Es empfiehlt sich also grundsätzlich, möglichst mit JOINS zu arbeiten.


Anzahl der Aufträge zu jeder Adresse. 

SELECT A.FIRMA1, (SELECT COUNT(*) FROM AUFTRAG AU WHERE AU.ADRESSE = A.ID) AS AUFTRAEGE FROM ADRESSEN A ORDER BY A.FIRMA1

Alternativer Aufruf für dbf-Tabellen: 

SELECT A.ID, A.FIRMA1, COUNT(AU.ID) AS AUFTRAEGE FROM ADRESSEN A JOIN AUFTRAG AU ON (A.ID = AU.ADRESSE) GROUP BY A.ID, A.FIRMA1 ORDER BY A.FIRMA1

Hinweis: Mit dem SUBSELECT kann ein Ergebnis aus einer beliebigen anderen Abfrage als Wert in die Feldliste eingefügt werden. Dabei ist zu beachten, dass in diesem Fall der SUB-Select nur einen Wert zurückgeben darf.


Ohne Tabellen-Alias ergibt sich ein völlig anderes Ergebnis 

SELECT FIRMA1, (SELECT COUNT(*) FROM AUFTRAG WHERE ADRESSE = ID) AS AUFTRAEGE FROM ADRESSEN ORDER BY FIRMA1

Hinweis: Um festzulegen welche Felder verglichen werden sollen, ist es wichtig ALIASE für die Tabellen festzulegen. Im Beispiel wird festgelegt dass das Feld Adresse aus der Tabelle AUFTRAG mit dem Feld ID aus der Adresse verglichen werden soll. Da in der Tabelle AUFTRAG ebenfalls ein Feld ID existiert, käme ohne die Verwendung der ALIASE ein anderes Ergebnis heraus, da nicht klar ist, welches Feld verwendet werden soll.

Ergebnis einschränken mit der WHERE-Klausel

Bearbeiten

Mit der WHERE-Klausel kann das Ergebnis einer Abfrage eingeschränkt werden. Es stehen die folgenden Operatoren zur Verfügung:

= (Ist gleich) != (Ungleich) < (Kleiner) > (Größer) <> (Ungleich) !< (Nicht kleiner als) !> (Nicht größer als) <= (Kleiner gleich) >= (Größer gleich) IS NULL (NULL-Wert, nicht die Zahl 0 oder Leerzeichen) BETWEEN <val> AND <val> ( Wert liegt zwischen) IN (<val>, <val>, ...) (Vergleichswert ist enthalten) LIKE (Teilweise Übereinstimmung. Kombination mit % und _ als Jokerzeichen) CONTAINING <val> (Enthält den Wert) STARTING WITH <val> (Vergleich: Anfang stimmt überein) CAST(), SUBSTRING(), EXTRACT(), UPPER(), LOWER() (Wie beschrieben)

In der WHERE-Klausel müssen die Filterwerte mit dem Datentyp des jeweiligen Feldes übereinstimmen. Numerische Werte sind ohne Hochkommata anzugeben. Dezimaltrennzeichen ist der Punkt. Datum, Zeit, und Textfelder müssen in einfachen Hochkommata eingeschlossen werden.


Vergleiche mit =, <, >, IS NULL

Bearbeiten

Alle Adressen mit FIRMA1 = "Hamburg" 

SELECT FIRMA1, PLZ, ORT FROM ADRESSEN WHERE FIRMA1 = 'Hamburg'

Bei folgender Abfrage wird "Hamburg" nicht gefunden. Die Vergleiche werden immer CASE-Sensitive ( Groß- und Kleinschreibung wird beachtet) durchgeführt. 

SELECT FIRMA1, PLZ, ORT FROM ADRESSEN WHERE FIRMA1 = 'hamburg'

Deshalb ist es besser in der WHERE-Klausel den Feldwert und den Suchwert in Großbuchstaben umzuwandeln: 

SELECT FIRMA1, PLZ, ORT FROM ADRESSEN WHERE UPPER(FIRMA1) = 'HAMBURG'

Anzahl Adressen ohne PLZ (NULL-Wert nicht Leerzeichen) 

SELECT COUNT(*) AS ANZAHL FROM ADRESSEN WHERE PLZ IS NULL

Anzahl Adressen ohne PLZ (NULL-Wert und Leerzeichen) 

SELECT COUNT(*) AS ANZAHL FROM ADRESSEN WHERE PLZ IS NULL OR PLZ =

Alle Adressen mit PLZ kleiner als 60000 

SELECT PLZ, ORT FROM ADRESSEN WHERE PLZ < '60000'

Alle Adressen mit PLZ größer als 60000 

SELECT PLZ, ORT FROM ADRESSEN WHERE PLZ > '60000'

BETWEEN - Sätze zwischen zwei Werten eingrenzen

Bearbeiten

Adressen deren PLZ von 60000 bis 70000 liegt. Beide Vergleichswerte sind enthalten. 

SELECT PLZ FROM ADRESSEN WHERE PLZ BETWEEN '60000' AND '70000'

Hinweis: Das gleiche Ergebnis würde WHERE PLZ >= '60000' AND PLZ <= '70000' liefern.

IN - Ein Wert aus einer Werteliste ist im Feld vorhanden

Bearbeiten

Nur Adressen mit den PLZ 25421 oder 20095 

SELECT PLZ, FIRMA1 FROM ADRESSEN WHERE PLZ IN ('25421', '20095')

Alle Adressen mit ID 1 oder 60 

SELECT ID, FIRMA1 FROM ADRESSEN WHERE ID IN (1,60)

Adressen, zu denen ein Auftrag existiert. Wird ermittelt mit einem SUB-Select 

SELECT ID,FIRMA1 FROM ADRESSEN WHERE ID IN (SELECT DISTINCT ADRESSE FROM AUFTRAG)

Hinweis: Mit einem SUB-SELECT wird eine zweite Abfrage gestartet, die als Ergebnis eine Werteliste liefert. Es werden in der Liste dann nur diese Sätze angezeigt. Im SUB-SELECT muss der Feldtyp des abgefragten Feldes dem des Feldes ID entsprechen. Durch die DISTINCT Einschränkung im SUB-SELECT wird gewährleistet, dass jede Adresse nur einmal gezählt wird auch wenn mehrere Aufträge zu der Adresse vorhanden sind.

Diese Variante ist je nach Datenbank sehr langsam wenn große Datenmengen vorhanden sind. Dasselbe Ergebnis kann mit einem JOIN erzielt werden. In Firebird ist die Verwendung eines JOINS in jedem Fall vorzuziehen wo es geht. Bei dbf-Tabellen muss das ausprobiert werden. Mehr zu JOINS später in Kapitel 9. 

SELECT DISTINCT A.ID, A.FIRMA1 FROM ADRESSEN A JOIN AUFTRAG AU ON(A.ID = AU.ADRESSE)

LIKE, CONTAINING, STARTING WITH

Bearbeiten

Alle Adressen, deren Namen mit "HAM" beginnt. Variante 1 

SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(FIRMA1) LIKE 'HAM%'

Hinweis: Das "%" Zeichen steht als Platzhalter (Jokerzeichen) für eine beliebige Anzahl beliebiger Zeichen analog zum "*" bei DOS. Der "_" steht als Platzhalter für genau ein beliebiges Zeichen analog zum "?" bei DOS. Die Jokerzeichen funktionieren nur zusammen mit LIKE!

Alle Adressen, deren Namen mit "HAM" beginnt. Variante 2 !!! NUR FIREBIRD !!! 

SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(FIRMA1) STARTING WITH 'HAM'

Alle Adressen, deren Namen mit "HAM" beginnt. Variante 3 

SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(SUBSTRING(FIRMA1 FROM 1 FOR 3)) = 'HAM'

Hinweis: Alle Varianten liefern das gleiche Ergebnis. Die Varianten 1 und 2 sind aber vorzuziehen, da keine zweite Umwandlung der Felder notwendig ist. Allerdings können in der STARTING-Variante keine Jokerzeichen verwendet werden. Jokerzeichen sind nur bei LIKE möglich.

Alle Adressen, deren Namen "BURG" enthält. Variante 1 

SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(FIRMA1) LIKE '%BURG%'

Alle Adressen, deren Namen "BURG" enthält. Variante 2 !!! NUR FIREBIRD !!! 

SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(FIRMA1) CONTAINING 'BURG'

Alle Adressen, deren Namen "B*RG" enthalten. Also ein "B" dann ein beliebiges Zeichen und dann "RG" 

SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(FIRMA1) LIKE '%B_RG%'

Wenn aber nach dem Zeichen "_" gesucht werden soll, muss dies mit der ESCAPE-Funktion angezeigt werden. Die Option ESCAPE '/' zeigt dem LIKE Befehl an, dass das Zeichen nach dem / nicht als Jokerzeichen interpretiert werden soll. Es kann auch ein beliebiges anderes Maskierungszeichen genommen werden. Sinnvoll ist natürlich ein Zeichen, dass möglichst nicht in der potentiellen Suchbedingung vorkommt. 

SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(FIRMA1) LIKE '%/_%' ESCAPE '/'

AND, OR, NOT, Klammersetzung

Bearbeiten

Es können mehrere WHERE-Klauseln mit OR und AND gekoppelt werden. Mit NOT wird die Umkehrung der Klausel oder der eingeklammerten Klauseln erreicht.


Alle Adressen mit Name "Berlin" oder "Hamburg". 

SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(FIRMA1) = 'HAMBURG' OR UPPER(FIRMA1) = 'BERLIN'

Alle außer Hamburg werden selektiert: 

SELECT FIRMA1 FROM ADRESSEN WHERE NOT UPPER(FIRMA1) = 'HAMBURG' ORDER BY FIRMA1

Alle außer Hamburg werden selektiert, deren ID kleiner 100 ist. Hier bezieht sich das NOT nur auf die erste Klausel. 

SELECT ID, FIRMA1 FROM ADRESSEN WHERE NOT UPPER(FIRMA1) LIKE 'HAMBURG%' AND ID < 100

Klammersetzung

Bearbeiten

Durch Klammersetzung können mehrere Klauseln zusammengefasst werden. NOT bezieht sich dann auf das Ergebnis der gesamten Klammer. So zeigt die folgende Klammersetzung etwas völlig anderes als vorher an. Nämlich alle Adressen außer Firma1 = Hamburg UND ID < 60. 

SELECT ID, FIRMA1 FROM ADRESSEN WHERE NOT (UPPER(FIRMA1) = 'HAMBURG' AND ID < 60)

Gesucht sind alle Adressen die entweder PLZ 25421 oder 20095 haben und FIRMA1 mit H anfängt 

SELECT PLZ,ORT,FIRMA1 FROM ADRESSEN WHERE PLZ = '25421' OR PLZ = '20095' AND FIRMA1 LIKE 'H%'

Hinweis: Hier werden alle Adressen angezeigt die 25421 ODER 20095 als PLZ haben und im Feld FIRMA mit "H" anfagen, also nicht das was wir wollten!

Richtig wäre die Abfrage so: 

SELECT PLZ,ORT,FIRMA1 FROM ADRESSEN WHERE (PLZ = '25421' OR PLZ = '20095') AND FIRMA1 LIKE 'H%'

Hinweis: Anhand dieses Beispiels wird deutlich, wie wichtig die richtige Klammersetzung ist. Im ersten Beispiel wird durch die OR Klausel der erste Teil vom zweiten Teil getrennt. Der zweite Teil wird als Einheit betrachtet, der zusammen wahr sein muss. Also werden alle Sätze angezeigt, die entweder 25421 als PLZ haben oder 20095 als PLZ haben und ein H am Anfang haben. Im zweiten Beispiel wird des Ergebnis aus den ersten beiden Klauseln zusammengefasst. Nur Sätze, die eines der beiden Kriterien erfüllen werden ausgewertet und dann noch durch das AND nur die Sätze, die ein H am Anfang haben. Grundsätzlich gilt: Alles was zusammen in Klammern steht wird als eine Einheit ausgewertet und muss alle in der Klammer befindlichen Bedingungen erfüllen um ein Ergebnis zu bringen. Die Kombination von beliebig vielen Klammern und AND oder OR Bedingungen ist möglich. Es muss nur sehr genau auf die Position der Klammern geachtet werden.

Noch eine Variante. Hier werden nur Sätze angezeigt die PLZ 20095 haben und FIRMA1 mit H anfängt. Die OR Klausel ist hier völlig sinnlos, da mit der ersten Einschränkung der PLZ nur Sätze vorkommen können, die als PLZ 20095 haben. OR PLZ = '25421' könnte also auch weggelassen werden. 

SELECT PLZ,ORT,FIRMA1 FROM ADRESSEN WHERE PLZ = '20095' AND (FIRMA1 LIKE 'H%' OR PLZ = '25421')

Funktionen in der WHERE-Klausel

Bearbeiten

Berechnungsfunktionen wie SUM(), AVG(), MAX(), COUNT() können nicht direkt in der WHERE-Klausel verwendet werden. (Ausnahme in einem Select in der WHERE-Klausel.) Funktionen wie EXTRACT, SUBSTRING, CAST, UPPER, LOWER können verwendet werden. In Firebird können auch Benutzerdefinierte Funktionen (UDF) verwendet werden. Siehe Kapitel 12.


Alle Adressen, die an einem 26. angelegt wurden 

SELECT ANLAGEDATUM, FIRMA1 FROM ADRESSEN WHERE EXTRACT(DAY FROM ANLAGEDATUM) = '26'

Alle Adressen, deren Name mit "HAM" anfängt 

SELECT FIRMA1 FROM ADRESSEN WHERE UPPER(SUBSTRING(FIRMA1 FROM 1 FOR 3)) = 'HAM'

SUBSELECT in der WHERE-Klausel

Bearbeiten

Es ist auch möglich in einer WHERE-Klausel das Ergebnis eines SUB-Selects zu verwenden. Alle Rechnungen deren Gesamtsumme größer-gleich dem Durchschnittswert aller Rechnungen ist: 

SELECT ID, GESAMT FROM RECHNUNG WHERE GESAMT >= (SELECT AVG(GESAMT) FROM RECHNUNG)

Die Rechnung mit dem höchsten Rechnungsbetrag soll angezeigt werden. 

SELECT ID, GESAMT FROM RECHNUNG WHERE GESAMT = (SELECT MAX(GESAMT) FROM RECHNUNG)

SUBSELECTs können mit verschiedenen Vergleichsoperatoren verwendet werden. EXISTS bedeutet, dass nur Sätze angezeigt werden, bei denen in der Unterabfrage ein Satz existiert. Wieviele Aufträge mit mindestens einer Rechnung gibt es. 

SELECT COUNT(*) AS AUFTRAGE FROM AUFTRAG A WHERE EXISTS (SELECT * FROM RECHNUNG R WHERE A.ID = R.AUFTRAG)

Die eben gezeigte Variante ist vergleichsweise langsam. Das gleiche Ergebnis ist mit einem JOIN wesentich schneller. Hier zeigt sich allem bei dbf-Tabellen der Unterschied. 

SELECT COUNT(DISTINCT A.ID) AS AUFTRAGE FROM AUFTRAG A JOIN RECHNUNG R ON(R.AUFTRAG = A.ID)

Gruppierung mit GROUP BY

Bearbeiten

GROUP BY wird verwendet um Zusammenfassungen zu berechnen. Grundsätzlich gilt: Jedes Feld, mit dem keine Berechnung stattfindet, sollte auch in der GROUP BY Klausel stehen. Es werden nur die Sätze zusammengefasst, die in den in der GROUP BY Bedingung angegebenen Feldern gleiche Werte haben. Bei dbf-Tabellen können keine Felder mit Funktionen gruppiert werden. In Firebird geht das. Die Reihenfolge der Gruppierung richtet sich nach der Reihenfolge, in der die Feldnamen in der GROUP-BY Klausel angeordnet sind. Es ist möglich die auszuwertenden Datensätze mit der WHERE-Klausel einzuschränken.


Gruppierung nach Feldern

Bearbeiten

Summierung der Rechnungsbeträge gruppiert nach Aufträgen: 

SELECT AUFTRAG, SUM(GESAMT) AS BETRAG FROM RECHNUNG GROUP BY AUFTRAG

Wenn zwei oder mehr Gruppierungsfelder angegeben werden, wird eine Summe jeweils gebildet, solange alle Gruppierungsfelder identisch sind. Im folgenden Beispiel werden die Aufträge noch getrennt summiert nach Auftragsnummer und Storno (1 oder 0) 

SELECT AUFTRAG, STORNO, SUM(GESAMT) AS BETRAG FROM RECHNUNG GROUP BY AUFTRAG, STORNO

Rechnungssumme zu allen Aufträgen, die nach dem 30.11.2005 erstellt wurden 

SELECT AUFTRAG, SUM(GESAMT) AS BETRAG FROM RECHNUNG WHERE DATUM >= '01.12.2005' GROUP BY DATUM, AUFTRAG

Rechnungssumme zu allen Aufträgen, die einen größeren Rechnungswert als 1000 haben 

SELECT AUFTRAG, SUM(GESAMT) AS BETRAG FROM RECHNUNG WHERE GESAMT >= 1000 GROUP BY AUFTRAG

Gruppierung mit Funktionen (ab Firebird 1.0 / 1.5)

Bearbeiten

Seit Firebird 1.5 ist es möglich auch UPPER, SUBSTRING EXTRACT und CAST in Gruppierungen zu verwenden. Dazu muss das Feld in der Feldliste die gleiche Funktion haben wie in der GROUP BY Klausel. Es darf aber in der GROUP By Klausel kein Aliasname für das Feld verwendet werden. Dies führt dazu, dass bei umfangreichen Funktionen leicht Fehler passieren. Ab Firebird 2.0 soll es möglich sein, in der GROUP BY Bedingung einfach den ALIAS der Funktion zu verwenden. Auch Gruppierungen mit benutzerdeifinerten Funktionen (UDF) sind möglich. Siehe Kapitel 10.


Im folgenden Beispiel soll angezeigt werden, wie viele Adressen es zu jedem Anfangsbuchstaben gibt. !!! NUR FIREBIRD !!! 

SELECT UPPER(SUBSTRING(FIRMA1 FROM 1 FOR 1)) AS ABC, COUNT(*) AS ANZAHL FROM ADRESSEN GROUP BY UPPER(SUBSTRING(FIRMA1 FROM 1 FOR 1))

Hinweis: Ab Firebird 2.0 geht das auch so: SELECT UPPER(SUBSTRING(FIRMA1 FROM 1 FOR 1)) AS ABC, COUNT(*) AS ANZAHL FROM ADRESSEN GROUP BY ABC.

HAVING - Klausel

Bearbeiten

Mit HAVING kann eine Abfrage mit Gruppierung eingeschränkt werden.


Aufträge, zu denen Rechnungen im Gesamtwert über 5000 existieren. Bei WHERE wird jeder Satz geprüft, es würden also nur Rechnungen summiert, die Gesamt > 5000 haben. Durch HAVING wird aber die Summe aller Rechungen zu einem Auftrag geprüft. 

SELECT AUFTRAG, COUNT(GESAMT) ANZAHL, SUM(GESAMT) SUMME FROM RECHNUNG GROUP BY AUFTRAG HAVING SUM(GESAMT) > 5000

Nun alle Aufträge, deren Rechnungssumme höher ist als der Durchschnittswert aus allen Rechnungen 

SELECT AUFTRAG, COUNT(GESAMT) ANZAHL, SUM(GESAMT) SUMME FROM RECHNUNG GROUP BY AUFTRAG HAVING SUM(GESAMT) > (SELECT AVG(GESAMT) FROM RECHNUNG)

Sortierung mit ORDER BY

Bearbeiten

Mit ORDER BY lassen sich Ergebnismengen sortieren. Durch die Sortierung entsteht ein schreibgeschützter Datensatzbereich, es sei denn für das Sortierfeld ist ein INDEX vorhanden. Es kann auch nach mehreren Feldern sortiert werden. Diese sind dann durch ein Komma zu trennen. Dabei gilt, dass zuerst nach dem ersten Feld sortiert wird, dann nach dem zweiten etc. Sortierungen lassen sich Aufsteigend und Absteigend durchführen. Dabei ist bei jdem Feld bei absteigender Sortierung "DESCENDING"oder für aufsteigende Sortierung "ASCENDING" anzugeben. Default ist ASCENDING, deshalb muss dies nicht zwingend angegeben werden.


ORDER BY

Bearbeiten

Alle Adressen aufsteigend sortiert nach FIRMA1: 

SELECT VORNAME, FIRMA1 FROM ADRESSEN ORDER BY FIRMA1

Alle Adressen absteigend sortiert nach FIRMA1: 

SELECT VORNAME, FIRMA1 FROM ADRESSEN ORDER BY FIRMA1 DESCENDING

Ab Firebird 1.5 kann festgelegt werden, wie mit NULL-Werten in der Sortierung umgegangen wird. Mit NULLS FIRST werden alle NULL-Werte zuerst angezeigt. Mit NULLS LAST alle NULL-Werte am Ende. Die Abfrage wird nach Vornamen sortiert und die NULL-Werte werden zuerst angezeigt !!! NUR FIREBIRD !!! 

SELECT VORNAME, FIRMA1 FROM ADRESSEN ORDER BY VORNAME NULLS FIRST

Analog dazu: !!! NUR FIREBIRD !!! 

SELECT VORNAME, FIRMA1 FROM ADRESSEN ORDER BY VORNAME NULLS LAST

Alle Adressen nach FIRMA1 und innerhalb FIRMA1 nach Vornamen sortieren 

SELECT VORNAME, FIRMA1 FROM ADRESSEN ORDER BY FIRMA1, VORNAME

Alle Adressen nach FIRMA1 und innerhalb FIRMA1 nach Vornamen absteigend sortieren 

SELECT VORNAME, FIRMA1 FROM ADRESSEN ORDER BY FIRMA1, VORNAME DESCENDING

Tabellen verbinden mit JOIN

Bearbeiten

Mit JOINS ist es möglich mehrere Tabellen miteinander zu verknüpfen. Im Prinzip bildet ein JOIN die Beziehungen der Tabellen untereinander ab. Hier zeigt sich auch oft, ob das zugrundeliegende Datenmodell stimmt.


INNER JOIN

Bearbeiten

Der gängigste JOIN ist der INNER JOIN oder auch einfach nur JOIN geschrieben. Beim INNER JOIN werden nur Datensätze angezeigt, die in beiden Tabellen eine Entsprechung haben.


Zu einigen Adressen gibt es Bankverbindungen in einer separaten Tabelle: 

SELECT A.FIRMA1, B.BANK, B.BLZ, B.KONTO 
FROM ADRESSEN A 
JOIN ADRESSENBANK B ON (B.NR = A.ID)

Hinweis: Als Ergebnis werden alle Adressen angezeigt, die eine Bankverbindung haben. Alle anderen Adressen werden nicht angezeigt. Adressen zu denen es zwei oder mehr Bankverbindungen gibt werden entsprechend häufig angezeigt.

Beispiel mit drei Tabellen. Die mittlere Tabelle bildet das Bindeglied zwischen den äußeren Tabellen. Zu verschiedenen Fahrzeugen gibt es Tankdaten (TANKVERBRAUCH) und dazu verschiedene Verbrauchsmittel (TAB_VERBRAUCH) Es werden alle Tankdaten zu jedem Fahrzeug angezeigt, und das entsprechende Verbrauchsmittel 

SELECT V.LITER, T.TEXT, F.TEXT AS FAHRZEUG FROM FAHRZEUG F 
JOIN TANKVERBRAUCH V ON (V.NR = F.ID) 
JOIN TAB_VERBRAUCH T ON (T.ID = V.ART)

OUTER JOIN

Bearbeiten

Mit OUTER JOINS werden ebenfalls Tabellen verknüpft, allerdings werden auch Sätze angezeigt, bei denen keine Übereinstimmungen vorliegen. Es gibt LEFT OUTER JOIN, RIGHT OUTER JOIN und FULL OUTER JOIN. Wie die Namen schon sagen, werden beim LEFT OUTER JOIN alle Sätze der links stehenden Tabelle angezeigt, RIGHT OUTER JOIN alle Sätze der rechten Tabelle und bei FULL OUTER JOIN alle Sätze. Dabei ist es egal ob es in der verknüpften Tabelle einen zugehörigen Satz gibt oder nicht.


Es werden alle Adressen angezeigt, egal ob sie eine Bankverbindung haben oder nicht. Wenn in der rechten Tabelle keine Entsprechung vorhanden ist, werden leere Felder für Bank, BLZ und Konto angezeigt. 

SELECT A.FIRMA1, B.BANK, B.BLZ, B.KONTO FROM ADRESSEN A 
LEFT OUTER JOIN ADRESSENBANK B ON (B.NR = A.ID)

Bei RIGHT OUTER JOIN werden alle Banksätze angezeigt, egal ob es eine Adresse gibt oder nicht (was natürlich bei gepflegten Daten nicht der Fall sein sollte). Allerdings werden keine Adressen angezeigt, für die es keine Bankverbindung gibt. 

SELECT A.FIRMA1, B.BANK, B.BLZ, B.KONTO FROM ADRESSEN A 
RIGHT OUTER JOIN ADRESSENBANK B ON (B.NR = A.ID) ORDER BY B.BANK

Bei FULL OUTER JOIN werden alle Adressen und alle Banksätze angezeigt, egal ob es Übereinstimmungen gibt. Beachten Sie, dass bei dbf-Tabellen immer das Feld in der Abfrage vorhanden sein muss, nach dem sortiert wird! 

SELECT A.FIRMA1, B.BANK, B.BLZ, B.KONTO FROM ADRESSEN A 
FULL OUTER JOIN ADRESSENBANK B ON (B.NR = A.ID) ORDER BY A.ID

Alternativer Aufruf für dbf-Tabellen: 

SELECT A.ID, A.FIRMA1, B.BANK, B.BLZ, B.KONTO FROM ADRESSEN A FULL OUTER JOIN ADRESSENBANK B ON (B.NR = A.ID) ORDER BY A.ID

Wir hatten das Beispiel mit den Tankdaten. Dort wurden keine Fahrzeuge angezeigt, zu denen es keine Tankdaten gibt. Um auch alle Fahzeuge anzuzeigen, die keine Tankdaten haben, muss mit einem OUTER JOIN gearbeitet werden. Dies funktioniert aber nur, wenn beide JOINS LEFT OUTER JOIN sind: 

SELECT V.LITER, T.TEXT, F.TEXT AS FAHRZEUG FROM FAHRZEUG F 
LEFT OUTER JOIN TANKVERBRAUCH V ON (V.NR = F.ID) 
LEFT OUTER JOIN TAB_VERBRAUCH T ON (T.ID = V.ART)

Im folgenden Beispiel werden alle Verbrauchsdaten nach Fahrzeug und Verbrauchsmittel gruppiert. Hier gibt es also eine Kombination von JOIN und GROUP BY 

SELECT SUM(V.LITER), T.TEXT, F.TEXT AS FAHRZEUG FROM FAHRZEUG F 
LEFT OUTER JOIN TANKVERBRAUCH V ON (V.NR = F.ID) 
LEFT OUTER JOIN TAB_VERBRAUCH T ON (T.ID = V.ART) 
GROUP BY F.TEXT, T.TEXT

Natürlich kann bei JOINS auch die WHERE-Klausel verwendet werden um das Ergebnis einzuschränken. Im folgenden Beispiel werden alle Verbrauchsdaten "Diesel" sowie die Kosten nach Fahrzeug und Verbrauchsmittel gruppiert 

SELECT SUM(V.LITER) AS LITER, SUM(V.BETRAG) AS KOSTEN, T.TEXT, F.TEXT AS FAHRZEUG 
FROM FAHRZEUG F 
LEFT OUTER JOIN TANKVERBRAUCH V ON (V.NR = F.ID) 
LEFT OUTER JOIN TAB_VERBRAUCH T ON (T.ID = V.ART) 
WHERE T.TEXT = 'Diesel' 
GROUP BY F.TEXT, T.TEXT

Mehrere Abfragen zusammenfassen mit UNION

Bearbeiten

Mit UNION können zwei oder mehr gleichartige SQL-Abfragen zu einem Datensatzbereich zusammengefasst werden. Die Feldnamen müssen dabei nicht gleich sein, aus Übersichtsgründen ist es aber besser. Die Feldtypen müssen aber für alle Abfragen identisch sein; auch in der Länge. Dies lässt sich zum Teil mit CAST oder SUBSTRING hinbiegen.


UNION

Bearbeiten

Im folgenden Beispiel sollen in einer Tabelle die Summen der einzelnen Verbrauchswerte und die Gesamtsumme aller Verbrauchswerte angezeigt werden: 

SELECT CAST('0' AS CHARACTER(1)) AS SORTIERUNG, T.TEXT AS ART, SUM(V.LITER) AS LITER 
FROM TANKVERBRAUCH V 
JOIN TAB_VERBRAUCH T ON (T.ID = V.ART) 
GROUP BY T.TEXT 
UNION SELECT CAST('1' AS CHARACTER(1)) AS SORTIERUNG, CAST('Gesamtsumme' AS CHARACTER(30)) AS ART, SUM(V1.LITER) AS LITER 
FROM TANKVERBRAUCH V1

Hinweis: Das Feld SORTIERUNG ist eine Krücke um die Datensätze richtig zu sortieren. Ansonsten würde der Satz Gesamtsumme mitten in die anderen Verbrauchsmittel sortiert und das soll ja nicht sein. Da keine Sortierung vorgegeben ist, werden die Daten nach dem ersten Feld sortiert ausgegeben. Ein Grid würde dann so eingestellt, dass das Feld "SORTIERUNG" nicht angezeigt wird.

Mit Union ist es auch möglich völlig unterschiedliche Tabellen zu einem Ergebnis zu kombinieren. In unserem Beispiel gibt es für die Fahrzeuge noch eine Abschreibungstabelle. Um nun die Verbrauchsmittel und die Abschreibung in eine Tabelle zu bekommen, würde sich zuerst ein JOIN anbieten: 

SELECT F.TEXT AS FAHRZEUG, SUM(V.LITER) AS LITER, SUM(A.EURO) AS ABSCHREIBUNG 
FROM FAHRZEUG F JOIN TANKVERBRAUCH V ON(F.ID = V.NR) 
JOIN ABSCHREIBUNG A ON (F.ID = A.NR) 
GROUP BY F.TEXT

Hinweis: Das Problem bei dieser Abfrage ist jedoch, dass sich hier die Sätze potenzieren. Für jeweils einen Abschreibungssatz gibt es alle Verbrauchssätze und umgekehrt. Im Ergebnis bedeutet dies völlig utopische Zahlen.

Mit UNION können nun die beiden Tabellen zusammengebracht werden: 

SELECT F.TEXT AS FAHRZEUG, SUM(V.LITER) AS LITER, CAST(0 AS NUMERIC(12,2)) AS ABSCHREIBUNG 
FROM FAHRZEUG F 
JOIN TANKVERBRAUCH V ON(F.ID = V.NR) 
GROUP BY F.TEXT 
UNION SELECT F1.TEXT AS FAHRZEUG, CAST(0 AS NUMERIC(12,2)) AS LITER, SUM(A.EURO) AS ABSCHREIBUNG  
FROM FAHRZEUG F1 JOIN ABSCHREIBUNG A ON(F1.ID = A.NR) 
GROUP BY F1.TEXT

Hinweis: Nachteil hierbei ist allerdings, dass es im Ergebnis für jede Art einen eigenen Satz gibt. Mit Firebird könnte dies mit einer Stored Procedure gelöst werden. (Eine Funktion, die auf dem Server direkt programmiert wird)

Die Lösung hierfür besteht in einem JOIN in Verbindung mit einem SUBSELECT. Das Verbindungsfeld F.ID muss auch in der Feldliste vorhanden sein. Leider funktioniert diese Variante nicht mit dbf-Tabellen. !!! NUR FIREBIRD !!! 

SELECT F.ID, F.TEXT AS FAHRZEUG, SUM(V.LITER) AS LITER,
(SELECT SUM(A.EURO) FROM ABSCHREIBUNG A WHERE F.ID = A.NR) AS ABSCHREIBUNG 
FROM FAHRZEUG F 
JOIN TANKVERBRAUCH V ON(F.ID = V.NR) 
GROUP BY F.TEXT, F.ID

Sonderfunktionen Firebird

Bearbeiten

Firebird bietet neben den Standard-SQL Funktionen wie SUBSTRING, UPPER etc. noch einige eigene Funktionen, die nicht zum SQL-Standard gehören.


SELECT FIRST SKIP (Ab Firebird 1.0)

Bearbeiten

Mit FIRST und SKIP ist es möglich nur bestimmte Sätze aus der Ergebnismenge anzuzeigen.


Nur der erste Datensatz wird zurückgegeben: !!! NUR FIREBIRD !!! 

SELECT FIRST (1) ID,FIRMA1 FROM ADRESSEN

Die ersten 5 Sätze werden nicht angezeigt: !!! NUR FIREBIRD !!! 

SELECT SKIP (5)  ID,FIRMA1 FROM ADRESSEN

Die ersten 5 Sätze werden übersprungen und dann werden die nächsten 10 Sätze angezeigt: !!! NUR FIREBIRD !!! 

SELECT FIRST(10) SKIP (5)  ID,FIRMA1 FROM ADRESSEN

Hinweis: Diese Funktion nicht in einen SUB-Select im Zusammenhang mit DELETE verwenden, sonst wird evtl. die komplette Tabelle gelöscht. Siehe auch Release-Notes Firebird 1.0

CASE WHEN <Bedingung> THEN <val> ELSE <val> END (ab FB 1.5)

Bearbeiten

Mit dieser neuen Funktion können Feldwerte schon in der Abfrage manipuliert werden. So kann beispielsweise je nach Bedingung ein anderes Feld angezeigt werden.


Im folgenden Beispiel gibt es zu Aufträgen eine Zuordnung entweder zu einer Anlage (Feld ANLAGE) oder zu einem Fahrzeug (Feld Fahrzeug). Die jeweiligen Stammdaten dazu sind in unterschiedlichen Tabellen gespeichert. Mit der CASE-Funktion ist es nun möglich den jeweils richtigen Wert aus einer der Tabellen im gleichen Feld anzeigen zu lassen. In diesem Beispiel wird die CASE-Funktion wie die IIF() Funktion in dbase verwendet. !!! NUR FIREBIRD !!! 

SELECT A.ID, 
CASE WHEN (A.FAHRZEUG IS NULL OR A.FAHRZEUG = 0) 
THEN AN.TEXT 
ELSE F.TEXT 
END AS ZUORDNUNG 
FROM AUFTRAG A  
LEFT OUTER JOIN TAB_ANLAGEN AN ON (A.ANLAGE = AN.ID) 
LEFT OUTER JOIN FAHRZEUG F ON (A.FAHRZEUG = F.ID) 
ORDER BY A.ID

Es ist aber auch möglich in der CASE-Funtkion mehrere Werte zu prüfen. Für jede zu prüfende Bedingung gibt es eine WHEN-Klausel und am Ende ein ELSE. Das ganze Konstrukt wird mit END abgeschlossen. Im folgenden Beispiel wird zu jedem Auftrag die Abrechnungsart angezeigt. !!! NUR FIREBIRD !!! 

SELECT ID, 
CASE WHEN ABRECH = 1 THEN 'jährlich' 
     WHEN ABRECH = 2 THEN 'halbjährlich'
     WHEN ABRECH = 3 THEN 'vierteljährlich' 
     WHEN ABRECH = 4 THEN 'monatlich' 
     WHEN ABRECH = 5 THEN 'keine' 
ELSE 'keine' 
END 
AS ABRECHNUNG 
FROM AUFTRAG ORDER BY ID

Hinweis: Die CASE-Funktion bietet einige Möglichkeiten, wie die Beispiele zeigen. So kann auf Server-Ebene schon einiges verarbeitet werden, was sonst erst später auf der Clientseite programmiert werden müsste.

COALESCE

Bearbeiten

Mit COALESCE kann eine Reihe von Werten angegeben werden, die nach NULL durchsucht werden. Der erste Wert, der nicht NULL ist wird zurückgegeben.


In der Adressentabelle gibt es vier Felder mit Telefonnummern (TELEFON1 - TELEFON4) Mit der COALESCE-Funktion kann nun eine Telefonnummer angezeigt werden, egal in welchem Feld sich die Telefonnummer befindet. Voraussetzung dafür ist aber dass leere Felder mit NULL belegt sind. !!! NUR FIREBIRD !!! 

SELECT FIRMA1 AS NAME, COALESCE(TELEFON1,TELEFON2,TELEFON3,TELEFON4) 
AS TELEFON FROM ADRESSEN ORDER BY FIRMA1

Wenn in keinem Feld ein Wert vorhanden ist, kann ein Vorgabewert festgelegt werden. !!! NUR FIREBIRD !!! 

SELECT FIRMA1 AS NAME, COALESCE(TELEFON1,TELEFON2,TELEFON3,TELEFON4,'unbekannt') AS TELEFON FROM ADRESSEN ORDER BY FIRMA1

NULLIF

Bearbeiten

Mit NULLIF kann ein Feld mit einem bestimmten Wert als NULL-Wert zurückgegeben werden.


In unserem obigen Beispiel sind oft die Telefonfelder nicht mit NULL-Werten belegt sondern mit Leerzeichen. Um nun aber trotzdem diese Felder mit der COALESCE-Funktion zu verwenden, kann ein leeres Telefonfeld mit NULLIF in NULL umgewandelt werden: !!! NUR FIREBIRD !!! 

SELECT FIRMA1 AS NAME, COALESCE(NULLIF(TELEFON1,), NULLIF(TELEFON2,), NULLIF(TELEFON3,), NULLIF(TELEFON4,),'unbekannt') AS TELEFON FROM ADRESSEN ORDER BY FIRMA1

Benutzerdefinierte Funktionen in Firebird (UDF)

Bearbeiten

Neben den fest in Firebird eingebauten Funktionen ist es auch möglich eigene Funktionen zu programmieren. Firebird bringt schon zwei Funktionsbibliotheken mit, in denen viele nützliche Funktionen enthalten sind. Am Beispiel der RTRIM-Funktion soll kurz die Umgangsweise mit Funktionen vorgestellt werden. Im Firebird Programmverzeichnis gibt es das Unterverzeichnis UDF. Dort befinden sich die DLL-Dateien für die UDFs (unter Linux heißen die Dateien *.so). In der ib_udf -Bibliothek gibt es die Funktion rtrim(). Ein TRIM-Befehl ist zur Zeit noch nicht in Firebird integriert. Allerdings wird TRIM in Firebird als reserviertes Wort deklariert, was darauf hindeutet, dass diese Funktion evtl. bald kommen wird. Um die externe Funktion nutzen zu können, muss diese für jede Datenbank einmalig deklariert werden. Hierzu dient der folgende Befehl:

DECLARE EXTERNAL FUNCTION rtrim 

  CSTRING(255)
  RETURNS CSTRING(255) FREE_IT
  ENTRY_POINT 'IB_UDF_rtrim' MODULE_NAME 'ib_udf';

Ist diese Deklaration erfolgt kann die Funktion ab sofort verwendet werden.

Die Beschreibung der Funktion ist jeweils in der zugehörigen .sql Datei enthalten, die ebenfalls im UDF-Verzeichnis liegen. Dort sind auch die Deklarationsanweisungen hinterlegt.

UDF´s können auch in der WHERE-Klausel und seit Version 1.0 auch in der GROUP BY-Klausel verwendet werden.


Beispiel RTRIM() Funktion

Bearbeiten

Im folgenden Beispiel wird das Feld FIRMA1 getrimt. 

SELECT RTRIM(FIRMA1) FROM ADRESSEN

Dies bringt jetzt natürlich kein aussagekräftiges Ergebnis. Interessant wird es, wenn zwei Felder zusammengefasst werden: 

SELECT RTRIM(FIRMA1)||', '||RTRIM(VORNAME) AS NAME FROM ADRESSEN

Grid-Bohrer UNION und Subselect

Bearbeiten

Aufbauend auf den vorhergehenden Beispielen sollen hier vor allem die Möglichkeiten aufgezeigt werden die sich mit SUBSELECTS und UNION bieten. Hauptgrund für deren intensive Verwendung ist auch hier wieder die einfache und übersichtliche Darstellung in Grids, oder die Verwendung als Datengrundlage in Reports. Im Gegensatz zu den bisherigen Beispielen habe ich nicht so sehr auf die Kompatibilität mit dbase-Tabellen geachtet. Ein großer Teil der Abfragen funktioniert so nicht mit dbase-Tabellen. Aber es geht hier vor allem darum, aufzuzeigen, welche Möglichkeiten sich mit UNION bieten. Letztendlich sollen die Beispiele vorwiegend als Denkanstöße und Anregung für eigene Problemstellungen dienen.


Umwandlung von Datentypen mit CAST

Bearbeiten

Vor allem bei der Verwendung von UNION wird besonders häufig die CAST-Funktion zur Umwandlung von Datentypen benutzt. Deshalb hier zur Erinnerung noch mal kurz die Syntax der CAST-Funktion.


Umwandlung einer Zahl in einen String und Umwandlung eines festen Wertes in einen String. 

SELECT CAST(GESAMT AS CHARACTER(10)) AS GESAMT, 
CAST('Summe' AS CHARACTER(10)) AS SUMME 
FROM RECHNUNG

Umwandlung eines String in eine Zahl 

SELECT CAST(PLZ AS NUMERIC(8,2)) AS PLZ 
FROM ADRESSEN

Verkettung von zwei Werten mit der CAST-Funktion. 

SELECT CAST(GESAMT AS CHARACTER(10))||' EUR' AS GESAMT   
FROM RECHNUNG

SUBSELECTS

Bearbeiten

SUBSELECTS werden immer dann verwendet, wenn die Aggregatfunktionen, JOIN, GROUP BY etc. nicht ausreichen um das gewünschte Ergebnis zu erzielen. Dabei sollte aber immer zuerst geprüft werden, ob das Ergebnis nicht doch mit den Standardfunktionen erreicht werden kann, denn SUBSELECTS können die Abfrage erheblich verlangsamen, da für jeden Satz eine zusätzliche SQL-Abfrage ausgeführt wird.


Im folgenden Beispiel wird für jeden Kunden der Umsatz ermittelt und gleichzeitig der %-Anteil am Gesamtumsatz angezeigt. Um den %-Anteil zu errechnen, muss die Gesamtsumme aller Rechnungen ermittelt werden, die dann mit dem Ergebnis der Gruppierung ins Verhältnis gesetzt wird. Hierzu wird in jedem Satz der Gesamtumsatz mit einem SUBSELECT ermittelt. Mit der Angabe ORDER BY 3 DESC wird das Ergebnis nach dem zweiten Feld (Umsatz) absteigend sortiert. 

SELECT A.ID, A.FIRMA1 AS NAME, 
SUM(R.GESAMT) AS UMSATZ, 
SUM(R.GESAMT) * 100 /
( SELECT SUM(GESAMT) FROM RECHNUNG ) 
AS PROZ_ANTEIL 
FROM RECHNUNG R 
JOIN AUFTRAG AU ON (R.AUFTRAG = AU.ID) 
JOIN ADRESSEN A ON (AU.ADRESSE = A.ID) 
GROUP BY A.ID, A.FIRMA1 
ORDER BY 3 DESC

In Firbird können wir durch die Angabe FIRST 10 auch nur die Top-Ten anzeigen lassen. 

SELECT FIRST 10 A.ID, A.FIRMA1 AS NAME, 
SUM(R.GESAMT) AS UMSATZ, 
SUM(R.GESAMT) * 100 /
( SELECT SUM(GESAMT) FROM RECHNUNG ) 
AS PROZ_ANTEIL 
FROM RECHNUNG R 
JOIN AUFTRAG AU ON (R.AUFTRAG = AU.ID) 
JOIN ADRESSEN A ON (AU.ADRESSE = A.ID) 
GROUP BY A.ID, A.FIRMA1 
ORDER BY 3 DESC

UNION mit einer Tabelle

Bearbeiten

Mit UNION werden zwei oder mehrere Abfragen hintereinander gehängt, die aber nur eine Ergebnisliste zurückgeben. Für die Verwendung von Grids ist diese Funktion optimal, denn so können zusätzliche Informationen in einem Grid angezeigt werden. Normalerweise werden mit UNION mehrere Abfragen mit gleichartigen Tabellen kombiniert. UNION kann aber auch zur mehrfachen Abfrage einer einzigen Tabelle verwendet werden.


In einer Verbrauchsliste soll der Verbrauch nach der jeweiligen Kraftstoffart sowie der Gesamtverbrauch aller Kraftstoffarten angezeigt werden. Hierzu werden zwei SELECT-Abfragen mit UNION zusammengehängt. Wichtig dabei ist, dass alle Feldtypen und Feldlängen übereinstimmen müssen. Um dies zu erreichen, werden die entsprechenden Werte mit CAST umgewandelt. 

SELECT 
CAST(T.TEXT AS CHARACTER(30)) AS ART, 
CAST(SUM(V.LITER) AS NUMERIC(12,2)) AS LITER 
FROM TANKVERBRAUCH V 
JOIN TAB_VERBRAUCH T ON (T.ID = V.ART) 
GROUP BY T.TEXT
 
UNION SELECT 
CAST('Gesamtsumme' AS CHARACTER(30)) AS ART, 
CAST(SUM(V1.LITER) AS NUMERIC(12,2)) AS LITER 
FROM TANKVERBRAUCH V1

Das Problem bei der vorigen Abfrage war die Sortierung nach dem ersten Wert. Somit wurde der Satz mit der Gesamtsumme zwischen den anderen Werten angezeigt was nicht gewünscht ist. Um dies zu beeinflussen, wird das erste Feld "Sortierung" als Hilfsmittel verwendet um die Sortierung zu steuern. Im Grid in einem Formular braucht dieses Feld ja dann nicht angezeigt werden. 

SELECT CAST('0' AS CHARACTER(1)) AS SORTIERUNG, 
CAST(T.TEXT AS CHARACTER(30)) AS ART, 
CAST(SUM(V.LITER) AS NUMERIC(12,2)) AS LITER 
FROM TANKVERBRAUCH V 
JOIN TAB_VERBRAUCH T ON (T.ID = V.ART) 
GROUP BY T.TEXT 
UNION SELECT CAST('2' AS CHARACTER(1)) AS SORTIERUNG, CAST('Gesamtsumme' AS CHARACTER(30)) AS ART, 
CAST(SUM(V1.LITER) AS NUMERIC(12,2)) AS LITER 
FROM TANKVERBRAUCH V1 
ORDER BY 1, 2

Um das ganze noch übersichtlicher zu gestalten kann noch eine Trennlinie eingefügt werden. Dazu wird ein weiterer SELECT eingefügt mit der Sortierung 1 in dem nur ein Satz aus einer beliebigen Tabelle (Hier wird das mit der Firebird Funktion FIRST 1 erreicht) abgefragt wird. In diesem Satz werden mit der CAST-Funktion die gewünschten Werte eingefügt. 

SELECT CAST('0' AS CHARACTER(1)) AS SORTIERUNG, CAST(T.TEXT AS CHARACTER(30)) AS ART, 
CAST(SUM(V.LITER) AS NUMERIC(12,2)) AS LITER 
FROM TANKVERBRAUCH V 
JOIN TAB_VERBRAUCH T ON (T.ID = V.ART) 
GROUP BY T.TEXT 
UNION SELECT FIRST 1 CAST('1' AS CHARACTER(1)) AS SORTIERUNG, 
CAST('------------------------------' AS CHARACTER(30)) AS ART, CAST(0 AS NUMERIC(12,2)) AS LITER 
FROM TANKVERBRAUCH V1 
UNION SELECT CAST('2' AS CHARACTER(1)) AS SORTIERUNG, 
CAST('Gesamtsumme' AS CHARACTER(30)) AS ART, 
CAST (SUM(V1.LITER) AS NUMERIC(12,2)) AS LITER 
FROM TANKVERBRAUCH V1 
ORDER BY 1, 2

Es ist auch möglich mit UNION eine Abfrage zu erstellen, die zwischen bestimmten Teilbereichen einen Trennstrich einfügt. Im Beispiel wird in einer Terminliste bei jedem neuen Tag ein Trennstrich eingefügt. Hierzu wird die selbe Tabelle mit dem selben Filter nochmals im UNION abgefragt. Damit pro Tag nur eine Zeile erscheint wird hier DISTINCT verwendet. Im UNION wird nur das Datum abgefragt und die restlichen Felder bleiben leer bzw. wird der Trennstrich eingefügt. Im Ergebnis sind nun die Sätze für jeden Tag zu sehen sowie für jeden Tag ein Trennsatz. Die Sortierung erfolgt nach Datum und Zeit. Da die Zeit im UNION leer ist wird dieser Satz immer zuerst angezeigt. 

SELECT CAST(T.DATUM AS DATE) DATUM, 
CAST(T.DATUM_ZEIT AS CHARACTER(5)) DATUM_ZEIT,   CAST(T.DATUM_ZEIT_BIS AS CHARACTER(5)) DATUM_ZEIT_BIS,   CAST(T.BETREFF AS CHARACTER(100)) BETREFF 
FROM TERMIN T 
WHERE T.DATUM >= '01.01.2007' AND T.DATUM <= '31.01.2007' 
UNION SELECT DISTINCT  CAST(T1.DATUM AS DATE) DATUM, 
CAST( AS CHARACTER(5)) DATUM_ZEIT, 
CAST( AS CHARACTER(5)) DATUM_ZEIT_BIS, 
CAST('-------------------------------------------------------------------------------' AS CHARACTER(100)) BETREFF 
FROM TERMIN T1 
WHERE  T1.DATUM >= '01.01.2007' AND T1.DATUM <= '31.01.2007' 
ORDER BY 1,2

UNION mit mehreren Tabellen

Bearbeiten

Mit UNION ist es auch möglich Tabellen mit völlig unterschiedlichen Strukturen zu verknüpfen. Dabei ist es nur wichtig das die Feldtypen und Feldlängen in allen Abfragen übereinstimmen. Um dies zu erreichen wird die Funktion CAST verwendet.


Zu jeder Adresse gibt es Termine und Aufträge. Zur Adresse 1 sollen alle Termine und Aufträge in einer Liste angezeigt werden. Sortierkriterium ist das Datum, dass es in beiden Tabellen gibt. Zuerst wird der SELECT für die Termine erstellt. Dabei wird das zweite Feld mit dem Text "Termin" erstellt und mit der Uhrzeit verkettet. Im zweiten SELECT werden die Aufträge abgefragt. Hier wird das zweite Feld mit dem Wert "Auftrag-Nr." gefüllt und mit der Auftragsnummer und der Anlage zu der dieser Auftrag zugeordnet ist (JOIN zur Tabelle TAB_ANLAGEN) verkettet. Als Ergebnis erhalten wir eine Liste mit allen Terminen und Aufträgen. 

SELECT CAST(T.DATUM AS DATE) DATUM, 
CAST('Termin: '||T.DATUM_ZEIT||'-'||DATUM_ZEIT_BIS AS CHARACTER(100)) AS INFO 
FROM TERMIN T 
WHERE  T.ID_ADRESSE = 1 
UNION SELECT CAST(A.DATUM AS DATE) DATUM, 
CAST('Auftrag-Nr. '||A.ID||' - '||TA.TEXT AS CHARACTER(100)) AS INFO 
FROM AUFTRAG A 
JOIN TAB_ANLAGEN TA ON (A.ANLAGE = TA.ID) 
WHERE A.ADRESSE = 1 
ORDER BY 1,2

Wenn nun zu der Liste noch die Rechnungsdaten dazukommen sollen, dann wird auch hier die Rechnungstabelle entsprechend zusammen geCASTet. 

SELECT CAST(T.DATUM AS DATE) DATUM, 
CAST('Termin: '||T.DATUM_ZEIT||'-'||DATUM_ZEIT_BIS AS CHARACTER(100)) AS INFO 
FROM TERMIN T 
WHERE  T.ID_ADRESSE = 1 
UNION SELECT CAST(A.DATUM AS DATE) DATUM, 
CAST('Auftrag-Nr. '||A.ID||' - '||TA.TEXT AS CHARACTER(100)) AS INFO 
FROM AUFTRAG A 
JOIN TAB_ANLAGEN TA ON (A.ANLAGE = TA.ID) 
WHERE A.ADRESSE = 1 
UNION SELECT CAST(R.DATUM AS DATE) DATUM, 
CAST('Rechnung-Nr. '||R.ID||' - '||R.GESAMT||' EUR'  AS CHARACTER(100)) INFO 
FROM RECHNUNG R 
JOIN AUFTRAG AU ON (AU.ID = R.AUFTRAG) 
WHERE AU.ADRESSE = 1 
ORDER BY 1,2

Farbige Grids

Bearbeiten

In Grids wäre es manchmal wünschenswert, wenn einzelne Zeilen andersfarbig angezeigt werden können. Zwar stellt dbase im Grid eine beforeCellPaint und onCellPaint Funktion bereit, mit der auch die Zellenfarbe beeinflußt werden kann. Diese sind aber nicht sehr komfortabel zu bedienen. Außerdem wird bei der Zuweisung einer anderen Farbe für eine Zelle auch der Markierungsbalken in dieser Spalte nicht mehr angezeigt (nur bei Versionen vor dBase 2.6). Seit dbase 2.5 ist es möglich, in einem Grid ein Editorfeld zu definieren. Da dbase in Editorfeldern rudimentäre HTML-Funktionen darstellen kann, kann man einen SQL mit der CAST-Funktion und Verkettungen so hinbasteln, dass auch farbiger Text etc. angezeigt werden kann. Da die HTML-Darstellung in Grids zuweilen recht träge ist, sollte man die Performance vorher ausgiebig testen.


Grundlage ist wieder der UNION-SQL mit den Terminen, Aufträgen und Rechnungen. Nur werden jetzt die Termine grün und Fett, die Aufträge rot und die Rechnungen blau und kursiv angezeigt. Bei der Zusammenstellung des CAST muss man beachten, dass die Länge des Feldes ausreichend ist. Die Länge muss mindestens die Länge aller Felder, des Textes und der HTML-Zeichen sein. 

SELECT CAST(T.DATUM AS DATE) DATUM,  
CAST('<font color=green><b>Termin: '||T.DATUM_ZEIT||'-'||DATUM_ZEIT_BIS||'</b></font>'  AS CHARACTER(200)) AS INFO  
FROM TERMIN T  WHERE T.ID_ADRESSE = 1  
UNION SELECT CAST(A.DATUM AS DATE) DATUM,  
CAST('<font color=red>Auftrag-Nr. '||A.ID||' - '||TA.TEXT||'</font>'  AS CHARACTER(200)) AS INFO  
FROM AUFTRAG A  JOIN TAB_ANLAGEN TA ON (A.ANLAGE = TA.ID)  
WHERE A.ADRESSE = 1  
UNION SELECT CAST(R.DATUM AS DATE) DATUM,  
CAST('<i><font color=blue>Rechnung-Nr. '||R.ID||' - '||R.GESAMT||' EUR'||'</font></i>'  AS CHARACTER(200)) AS INFO  
FROM RECHNUNG R  JOIN AUFTRAG AU ON (AU.ID = R.AUFTRAG)  
WHERE AU.ADRESSE = 1  ORDER BY 1,2


Die Möglichkeiten mit HTML-Tags erweitern sich bei Firebird noch deutlich mehr, wenn die Funktion CASE verwendet wird. Mit CASE kann in Abhängigkeit eines Feldwertes ein jeweils anderes Ergebnis ausgegeben werden. Im Beispiel werden alle Termine die nach dem 31.1.2007 liegen blau angezeigt, alle Termine zwischen dem 1.1. und 31.1.2007 rot und alle Termine vor dem 1.1.2007 schwarz. 

SELECT CAST(T.DATUM AS DATE) DATUM, CASE WHEN DATUM > '31.01.2007' 
THEN CAST('<font color=blue><b>Termin: '||T.DATUM_ZEIT||'-'||DATUM_ZEIT_BIS||'</b></font>' AS CHARACTER(200)) 
WHEN DATUM >= '01.01.2007' AND DATUM <= '31.01.2007' 
THEN CAST('<font color=red><b>Termin: '||T.DATUM_ZEIT||'-'||DATUM_ZEIT_BIS||'</b>' AS CHARACTER(200)) 
ELSE CAST('<font color=black><b>Termin: '||T.DATUM_ZEIT||'-'||DATUM_ZEIT_BIS||'</b></font>' AS CHARACTER(200)) END AS INFO  
FROM TERMIN T  WHERE T.ID_ADRESSE = 1 ORDER BY 1 DESC
Abgerufen von „https://de.wikibooks.org/w/index.php?title=Programmieren_mit_dBASE_PLUS:_SQL-Datenbanken:_Einführung_in_die_SQL-Syntax&oldid=959435“