Office Forum
www.Office-Loesung.de
Access :: Excel :: Outlook :: PowerPoint :: Word :: Office :: Wieder Online ---> provisorisches Office Forum <-
SQL-Rezepte
zurück: Grundlagentraining von Microsoft weiter: Datensatznavigation / Suche in grossen Tabellen Unbeantwortete Beiträge anzeigen
Neues Thema eröffnen   Neue Antwort erstellen     Status: Tutorial Facebook-Likes Diese Seite Freunden empfehlen
Zu Browser-Favoriten hinzufügen
Autor Nachricht
Nouba
nicht ganz unwissend :)


Verfasst am:
05. Nov 2006, 19:52
Rufname:
Wohnort: Berlin

SQL-Rezepte - SQL-Rezepte

Nach oben
       Version: (keine Angabe möglich)

Hallo, Freunde der SQL-Abfragen

im folgenden will ich einige kleine Tipps & Tricks zeigen, mit denen man verschiedene Problemsituationen in Jet-SQL lösen kann. Im Laufe der Zeit habe ich vor, diesen Beitrag nach und nach mit weiteren SQL-spezifischen Themen zu ergänzen.

Ich versuche dabei in diesem Beitrag von VBA-Funktionen Abstand zu nehmen, weil wir doch SQL als Thema gewählt haben. In dem einen oder anderen Fall wird ein völliger Verzicht jedoch nicht immer ausbleiben können. Alle verwendeten Beispieltabellen werden in Form von DDL-Abfragen dargestellt und können jeweils einzeln in die SQL-Ansicht einer Abfrage kopiert und danach ausgeführt werden. Ein ergänzender Datenabschnitt folgt nötigenfalls daran anschließend. Zum einfachen Import der mit einem Tabulatorzeichen getrennten Werte empfehle ich, den Text in einen Editor (Notepad, Wordpad, o. .) zu kopieren und als Textdatei mit der Endung txt abzuspeichern. In Access legt man danach eine Verknüpfung auf diese Datei an und erstellt eine Anfügeabfrage über die angelegte Verknüpfung, die die Daten ans eigentliche Ziel transportiert.

1. Pivot-Tabellen

Mit Pivot-Tabellen lassen sich Aufgabenstellungen in SQL lösen, die Wertesequenzen zwischen einem Anfangs- und Endwert erfordern. Man kann damit zum Beispiel ASCII-Zeichen in einer Abfrage generieren oder auch einfach nur die nächsten sieben Tage einer Woche ermitteln. Die Pivot-Tabelle muss dafür natürlich groß genug ausgelegt sein, um sie erfolgreich einsetzen zu können. Ich selbst habe in vielen meiner Datenbanken ein solche Tabelle mit 1000 Werten (im weiteren T1000 genannt), womit ich die meisten Anforderungen im Alltag meistern kann. Es mag aber auch Situationen geben, die mehrere, verschieden große Pivot-Tabellen parallel in einer Datenbank rechtfertigen.

Kommen wir nun dazu, die Pivot-Tabelle (T1000) anzulegen. Bei tausend Werten wählen wie ein Zahlenfeld mit der Feldgröße Integer für unsere Spalte I aus und legen es als Primrschlüsselfeld aus, damit wir keine doppelten Werte erhalten.

Code:
CREATE TABLE T1000 ( I SHORT PRIMARY KEY );

Danach legen wir uns eine kleine Hilfstabelle (THilfe), die wir zum Befüllen unserer Pivot-Tabelle verwenden wollen, an.

Code:
CREATE TABLE THilfe ( C CHAR(1) );

Nun sollte man die Hilfstabelle (THilfe) mit den Werten '0' - '9' bestücken, was manuell vermutlich am schnellsten zu erledigen ist. Ansonsten kann man auch zehn Anfügeabfragen in folgender Form nacheinander ausführen.

Code:
INSERT INTO THilfe VALUES ( '0' );
INSERT INTO THilfe VALUES ( '1' );
INSERT INTO THilfe VALUES ( '2' );
INSERT INTO THilfe VALUES ( '3' );
INSERT INTO THilfe VALUES ( '4' );
INSERT INTO THilfe VALUES ( '5' );
INSERT INTO THilfe VALUES ( '6' );
INSERT INTO THilfe VALUES ( '7' );
INSERT INTO THilfe VALUES ( '8' );
INSERT INTO THilfe VALUES ( '9' );

Mit der nächsten Anfügeabfrage erstellen wir genau 1000 Datensätze, indem die Hilfstabelle dreimal in der FROM-Klausel aufgeführt wird. D. h., jeder Spaltenwert wird mit jedem Spaltenwert der anderen beiden Tabellen genau einmal kombiniert. Die Umwandlung der Zeichenkette in eine Zahl vollzieht dann Access von alleine.

Code:
INSERT INTO T1000
SELECT H1.C & H2.C & H3.C AS I
FROM   THilfe AS H1,
       THilfe AS H2,
       THilfe AS H3

Die Hilfstabelle wird nun nicht mehr benötigt und kann in die Tonne gestampft werden.

Code:
DROP TABLE THilfe;

Hier werden jetzt einige Anwendungen dieser Pivot-Tabelle aufgezeigt. Wir wollen zunächst alle darstellbaren ASCII-Zeichen zwischen 32 (dem Leerzeichen) und 126 (~) ermitteln.

Code:
SELECT P.I AS ASCII,
       Chr$( P.I ) AS ZCHN
FROM   T1000 AS P
WHERE  P.I BETWEEN 32 AND 126;

Uns interessieren die nächsten sieben Wochentage ab Morgen.

Code:
SELECT Date() + P.I AS DATUM,
       Format$( Date() + P.I, "dddd" ) AS TAG
FROM T1000 AS P
WHERE  P.I BETWEEN 1 AND 7;

Wir wollen eine Monatsübersicht des laufenden Monats nach Kalenderwochen erstellen.

Code:
SELECT   Z.KW AS KW,
         Max( IIf( Z.TW = 1, Z.TM ) ) AS MO,
         Max( IIf( Z.TW = 2, Z.TM ) ) AS DI,
         Max( IIf( Z.TW = 3, Z.TM ) ) AS MI,
         Max( IIf( Z.TW = 4, Z.TM ) ) AS DO,
         Max( IIf( Z.TW = 5, Z.TM ) ) AS FR,
         Max( IIf( Z.TW = 6, Z.TM ) ) AS SA,
         Max( IIf( Z.TW = 7, Z.TM ) ) AS SO
FROM     ( SELECT DatePart( "ww", Y.TJ, 2, 2 ) AS KW,
                  Day( Y.TJ ) AS TM,
                  DatePart( "w", Y.TJ, 2, 2 )  AS TW
           FROM   ( SELECT   Date() - Day( Date() ) + 1 + P.I AS TJ
                    FROM     T1000 AS P
                    WHERE    P.I < 31 AND
                             Month( Date() - Day( Date() ) + 1 + P.I )
                               = Month( Date() ) ) AS Y ) AS Z
GROUP BY Z.KW;

2. Mengen

der folgende Abschnitt behandelt Operationen mit Datenmengen, wie sie dem einen oder anderen aus der Mengenlehre in der Mathematik bekannt sein werden.

Es gibt drei wichtige Komponenten, die man bei der Arbeit mit Mengen kennen sollte. Zuerst sei die Menge selbst erwähnt, die sich als einer Ansammlung von Elementen versteht. Als Element einer Datenmenge betrachten wir einen einzelnen Datensatz einer Datentabelle bzw. einer SQL-Abfrage. In der Mathematik darf eine Menge kein Element mehr als einmal enthalten, was in Datentabellen nicht (immer) der Fall sein muß. Die Reihenfolge, in der die Elemente in der Menge enthalten sind, ist nicht festgelegt. Die SQL-Sprache erlaubt es uns aber, die Mengen in einer bestimmten Reihenfolge zu sortieren, um so z. Bsp. die ersten drei oder die letzten fünf Elemente (Datensätze) zu extrahieren.

Um in der Praxis zu bleiben, betrachten wir die Tabelle Bestelldetails der Nordwind-Beispieldatenbank. Hier ließe sich für jede eindeutige Bestell-Nr die Teilmenge der dazu bestellten Artikel als Elemente (Datensätze) ermitteln.

Code:
CREATE TABLE Bestelldetails (
  [Bestell-Nr]       INTEGER NOT NULL,
  [Artikel-Nr]       INTEGER NOT NULL,
  Einzelpreis        CURRENCY NOT NULL,
  Anzahl             INTEGER NOT NULL,
  Rabatt             FLOAT NOT NULL,
  PRIMARY KEY ( [Bestell-Nr], [Artikel-Nr] ) );

Um alle Datensätze einer Bestellung aufzulisten, genügt es, in einer Abfrage ein entsprechendes Kriterium für die Bestell-Nr festzulegen.

Code:
SELECT * FROM Bestelldetails WHERE [Bestell-Nr] = 217;

In der Mathematik stellen Elemente die (unteilbaren) Mitglieder einer Menge dar. In der Datenbankpraxis lassen sich Elemente (Datensätze) jedoch sehr wohl weiter untergliedern, sofern mehrere Spalten vorhanden sind. Man könnte z. Bsp. alle Bestellungen ermitteln wollen, die mindestens ein Element mit einem Rabatt von 5% enthalten.

Code:
SELECT   B.[Bestell-Nr]
FROM     Bestelldetails AS B
GROUP BY B.[Bestell-Nr]
HAVING   EXISTS ( SELECT *
                  FROM   Bestelldetails AS B1
                  WHERE  B1.Rabatt = 0.05 AND
                         B.[Bestell-Nr] = B1.[Bestell-Nr] );

Operationen mit Mengen

Operationen mit zwei Mengen erlauben unterschiedliche Vergleiche. Das Ergebnis hängt dabei von der Art der Operation ab. Bei Mengenoperationen kann folgendes ermittelt werden: die Teilmenge, die Schnittmenge, die Vereinigungsmenge, die Differenzmenge (Komplementärmenge), die Verbindungsmenge (auch als kartesisches Produkt bezeichnet), die Potenzmenge. Mehr dazu kann man unter Mengenlehre in der Wikipedia nachlesen.

Ein weiteres Beispiel für die Bildung einer Teilmenge: Liste alle Bestellungen, die Artikel-Nr 48 (Chocolade) beinhalten.

Code:
SELECT B.*
FROM   Bestellungen AS B
WHERE  EXISTS ( SELECT *
                FROM   Bestelldetails AS BD
                WHERE  BD.[Bestell-Nr] = B.[Bestell-Nr] AND
                       BD.[Artikel-Nr] = 48 );

Kommen wir nun zu einer weiteren Art zur Bildung von Teilmengen. Andere SQL-Dialekten kennen dafür das Schlüsselwort INTERSECT, welches in Jet-SQL leider nicht zur Verfügung steht. Man kann in Access aber zu diesem Zweck mit zwei (oder mehreren) verknüpften Unterabfragen diese Funktionalität nachbilden.

Hier folgt ein Beispiel bezogen auf die Nordwind.mdb: Finde alle Kunden, die in ihren Bestellungen sowohl die Artikel-Nr 55 als auch die Artikel-Nr 13 aufgeführt haben.

Code:
SELECT DISTINCT B.[Kunden-Code]
FROM            ( ( SELECT BD.[Bestell-Nr]
                    FROM Bestelldetails AS BD
                    WHERE BD.[Artikel-Nr] = 55 ) AS I1
                INNER JOIN
                  ( SELECT BD.[Bestell-Nr]
                    FROM   Bestelldetails AS BD
                    WHERE  BD.[Artikel-Nr] = 13 ) AS I2
                ON I1.[Bestell-Nr] = I2.[Bestell-Nr] )
INNER JOIN      Bestellungen AS B
ON              I1.[Bestell-Nr] = B.[Bestell-Nr];

Um die Differenz zweier Mengen bilden zu können, kann man wieder den EXISTS-Operator einsetzen - diesmal jedoch mit einer Verneinung des Ausdrucks.

Beispiel: Wir wollen aus der Nordwind-Db erfahren, welche Produkte im ersten Quartal 1997 gekauft wurden, die nicht im zweiten Quartal gekauft wurden.

Code:
SELECT DISTINCT BD.[Artikel-Nr]
FROM   Bestellungen AS B
       INNER JOIN Bestelldetails AS BD
       ON B.[Bestell-Nr] = BD.[Bestell-Nr]
WHERE  Year([B].[Bestelldatum]) * 4 + DatePart("q", B.Bestelldatum) = 1997 * 4 + 1 AND
       Not Exists ( SELECT *
                    FROM   Bestellungen AS B1
                           INNER JOIN Bestelldetails AS BD1
                           ON B1.[Bestell-Nr] = BD1.[Bestell-Nr]
                    WHERE  BD1.[Artikel-Nr] = BD.[Artikel-Nr] AND
                           Year( B1.Bestelldatum ) * 4 + DatePart( "q", B1.Bestelldatum ) = 1997 * 4 + 2 );

Recht einfach gestaltet sich die folgende Aufgabe: Liste alle 1997 bestellten Artikel-Nummern, deren Bestellmenge größer gleich 200 war.

Code:
SELECT   BD.[Artikel-Nr], Sum(BD.Anzahl) AS Verkauft_in_1997
FROM     Bestellungen AS B
         INNER JOIN Bestelldetails AS BD
         ON B.[Bestell-Nr] = BD.[Bestell-Nr]
WHERE    Year( B.Bestelldatum ) = 1997
GROUP BY BD.[Artikel-Nr]
HAVING   Sum( BD.Anzahl ) >= 200
ORDER BY Sum( BD.Anzahl ) DESC;

Für Kritik und weitere Anregungen zum Thema bin ich selbstverständlich zu haben.

_________________
mit freundlichen Grüssen Nouba

Wenn beim Lesen eines Beitrags der Eindruck entsteht, dass sich der Fragesteller wenig Mühe gegeben hat, so erhöht das nicht unbedingt die Motivation, eine Antwort zu verfassen.


Zuletzt bearbeitet von Nouba am 17. März 2007, 12:26, insgesamt 9-mal bearbeitet
stpimi
Moderator Access


Verfasst am:
05. Nov 2006, 21:38
Rufname:
Wohnort: Graz


AW: SQL-Rezepte - AW: SQL-Rezepte

Nach oben
       Version: (keine Angabe möglich)

Servus Norbert!

Ich denke, ich muss mir einige neue Superlative überlegen, die bisher gebräuchlichen habe ich für Deine Tipps schon verwendet ... Very Happy

Danke auch für diesen Tipp!

_________________
Lg, Michael

Dein Feedback hilft auch anderen - vergiß es nicht!
Stefffano
Dazugelernt


Verfasst am:
24. Nov 2006, 19:38
Rufname:
Wohnort: Chemnitz

AW: SQL-Rezepte - AW: SQL-Rezepte

Nach oben
       Version: (keine Angabe möglich)

Hallo Nouba,
hoffe, Du bist auch für Lobs zu haben Razz

Deine gut kommentierten Anleitungen nützen hoffentlich Vielen viel.
Schöne Grüße, Stefan
Gast



Verfasst am:
21. Jul 2011, 15:36
Rufname:


AW: SQL-Rezepte - AW: SQL-Rezepte

Nach oben
       Version: (keine Angabe möglich)

Bin zwar etwas spät dran aber der IN und der EXIST Quantor sind in JET SQL nicht besonders effizient. Die entsprechende Unterabfrage wird offenbar so oft ausgeführt, wie die EXIST Abfrage Elemente hat. Besser ist ein JOIN, um INTESECT bzw. EXCEPT zu simulieren. Als einfaches Beispiel zwei Tabellen mit jeweils nur der Spalte "ID":
Tabelle A(ID): {1,2,3,4,5}
Tabelle B(ID): {3,4,5,6,7,8,9}
A INTERSECT B : {3,4,5}
Code:
SELECT A.ID
FROM   A
       INNER JOIN B
       ON A.ID=B.ID
A EXCEPT B : {1,2}
Code:
SELECT ID
FROM   (SELECT A.ID, B.ID AS ID2
        FROM   A
               LEFT JOIN B
               ON A.ID=B.ID)
WHERE  IsNull(ID2)
Aufeinanderfolgende Elemente einer Tabelle können elegant mit MIN gesucht werden. Das nächste Vielfache von ID in Tabelle B wäre z.B. {(3,6); (4,8)}
Code:
SELECT   ID, Min(nID) AS ID2
FROM     (SELECT b1.ID, b2.ID AS nID, b2.ID mod b1.ID AS modulo
          FROM   B AS b1, B AS b2)
WHERE    modulo=0
AND      ID<nID
GROUP BY ID
Neues Thema eröffnen   Neue Antwort erstellen Alle Zeiten sind
GMT + 1 Stunde

Diese Seite Freunden empfehlen

Seite 1 von 1
Gehe zu:  
Du kannst Beiträge in dieses Forum schreiben.
Du kannst auf Beiträge in diesem Forum antworten.
Du kannst deine Beiträge in diesem Forum nicht bearbeiten.
Du kannst deine Beiträge in diesem Forum nicht löschen.
Du kannst an Umfragen in diesem Forum nicht mitmachen.
Du kannst Dateien in diesem Forum nicht posten
Du kannst Dateien in diesem Forum herunterladen

----> Diese Seite Freunden empfehlen <------ Impressum - Besuchen Sie auch: Microsoft Excel-Formeln