SQL-Beispiele aus dem Miro-Board (04 SQL-Vertiefung, Fortsetzung)
Subqueries
SELECT COUNT(*) FROM ware;
SELECT sin((SELECT COUNT(*) FROM ware)); -- nicht sehr sinnvoll
-- SELECT sin(SELECT COUNT(*) FROM ware); -- Klammer um Argument fehlt
SELECT EXISTS (SELECT * FROM ware WHERE w_bezeichnung = 'Eieruhr');
SELECT ('Eieruhr', 'Sonstiges') IN (SELECT w_bezeichnung, w_typ FROM ware);
SELECT ROW('Eieruhr', 'Sonstiges') IN (SELECT w_bezeichnung, w_typ FROM ware);
Select-Klausel
/***************************************************************************
* Sammeln von eindeutigen Informationen aus verschiedenen Tabellen.
* Das folgende Beispiel ist nicht sonderlich sinnvoll, aber korrekt.
***************************************************************************/
SELECT
(SELECT h_name FROM haendler WHERE h_id = 1) AS haendler,
(SELECT w_bezeichnung FROM ware WHERE w_id = 3) AS ware,
(SELECT w_bezeichnung FROM ware WHERE w_id = 999) AS ware
--FROM one
;
-- Funktioniert nicht, da es mehrere Händler gibt:
SELECT
(SELECT h_name FROM haendler WHERE h_id > 1) AS haendler
;
-- Funktioniert nicht, da es mehrere Attribute gibt:
SELECT
(SELECT h_name, h_ortschaft FROM haendler WHERE h_id = 1) AS haendler
;
-- Funktioniert, da es nur ein Attribut gibt:
SELECT
(SELECT ROW(h_name, h_ortschaft) FROM haendler WHERE h_id = 1) AS haendler
;
Korrelierte vs. nicht-korrelierte Subquery
/***************************************************************************
* Wie viel Prozent des gesamten Warenangebots liefert jeder Händler?
* Beachte: Man muss 100.0 an Stelle von 100 schreiben, um eine Float-
* Division an Stelle einer Integer-Division zu erzwingen.
***************************************************************************/
SELECT h.h_id, h.h_name, h.h_ortschaft,
-- absolute Zahl der Waren, die Händler h liefert
COUNT(DISTINCT l.w_id)
AS anzahl_waren_je_haendler,
-- prozentualer Anteil der Waren, die Händler h liefert
-- (mit Subquery zur Ermittlung der Anzahl aller Waren)
ROUND(100.0*COUNT(DISTINCT l.w_id)/(SELECT COUNT(*) FROM ware), 2)
AS prozent_waren
FROM haendler h LEFT JOIN liefert l ON h.h_id = l.h_id
GROUP BY h.h_id, h.h_name, h.h_ortschaft
ORDER BY prozent_waren DESC, h_id
;
SELECT h_id,
(SELECT COUNT(DISTINCT w_id) -- korrelierte Subquery
FROM liefert l
WHERE l.h_id = h.h_id -- Zugriff auf h.h_id,
) AS h_anzahl_waren, -- außerhalb der Subquery definiert
(SELECT COUNT(w_id) -- nicht-korrelierte Subquery
FROM ware
) AS w_anzahl_alle_waren
FROM haendler h
;
Durchnummerieren
/***************************************************************************
* Jede Ware soll mit einer laufenden Nummer versehen werden, in der
* alphabethischen Reihenfolge der Bezeichnungen:
***************************************************************************/
SELECT (SELECT COUNT(*)+1
FROM ware w2
WHERE w2.w_bezeichnung < w1.w_bezeichnung
) AS nr,
w1.w_bezeichnung
FROM ware w1
ORDER BY nr
;
-- Besser (da performanter): Window-Funktion verwenden:
SELECT COUNT(*) OVER (ORDER BY w_bezeichnung) AS nr,
w_bezeichnung
FROM ware
ORDER BY nr
;
-- oder
SELECT RANK() OVER (ORDER BY w_bezeichnung) AS nr,
w_bezeichnung
FROM ware
ORDER BY nr
;
SELECT DENSE_RANK() OVER (ORDER BY w_bezeichnung) AS nr,
w_bezeichnung
FROM ware
ORDER BY nr
;
From-Klausel
/***************************************************************************
* Sinnloses, aber einfaches Beispiel
***************************************************************************/
-- SQL-Standard-konform, aber funktioniert in PostgreSQL nicht
/*
SELECT h_id
FROM (SELECT DISTINCT h_id, w_id FROM liefert)
;
*/
-- PostgreSQL: Man muss für eine Subquery in einer From-Klause
-- einen Alias-Namen vergeben!
SELECT h_id
FROM (SELECT DISTINCT h_id, w_id FROM liefert) AS dummy
;
SELECT h_id
FROM (SELECT DISTINCT h_id, w_id FROM liefert) _
;
Weitere Beispiele
/*******************************************************************************
* Wie viele verschiedene Waren liefern die jeweiligen Händler?
* Beachten Sie, dass eine Händler ein und dieselbe Ware
* mehrfach zu unterschiedlichen Konditionen liefern kann!
*******************************************************************************/
SELECT h_id, COUNT(*) AS l_anzahl_waren
FROM (SELECT DISTINCT h_id, w_id FROM liefert) AS _
GROUP BY h_id
ORDER BY h_id
;
-- Das funktioniert allerdings auch ohne Subquery!
SELECT h_id, COUNT(DISTINCT w_id) AS l_anzahl_waren
FROM liefert
GROUP BY h_id
ORDER BY h_id
;
-- Es geht auch ohne GROUP BY mit einer Subquery in der SELECT-Klausel.
SELECT h_id,
(SELECT COUNT(DISTINCT w_id)
FROM liefert l
WHERE l.h_id = h.h_id
) AS l_anzahl_waren
FROM haendler h
;
/* Ohne DISTINCT wäre das Ergebnis fehlerhaft!
Zum Beispiel liefert Händler 4 ein und dieselbe Ware zu
unterschiedlichen Preisen und Konditionen. Gefragt war
aber die Anzahl der UNTERSCHIEDLICHEN Waren, die ein
Händler liefert.
*/
Weitere Beispiele (Fortsetzung)
/* Das Ganze noch einmal mit Angabe von Händlernamen und -ortschaft. */
SELECT h.h_id, h.h_name, h.h_ortschaft, COUNT(w_id) AS l_anzahl_waren
FROM haendler h
LEFT JOIN (SELECT DISTINCT h_id, w_id FROM liefert) l
ON h.h_id = l.h_id
GROUP BY h.h_id, h.h_name, h.h_ortschaft
ORDER BY h_id
;
/* Die Subquery kann man auch mit einer (temporären) View berechnen */
WITH l(h_id, w_id)
AS NOT MATERIALIZED
(SELECT DISTINCT h_id, w_id FROM liefert)
SELECT h.h_id, h.h_name, h.h_ortschaft, COUNT(*) AS l_anzahl_waren
FROM haendler h LEFT JOIN l ON h.h_id = l.h_id
GROUP BY h.h_id, h.h_name, h.h_ortschaft
ORDER BY h_id
;
-- ohne Subquery
SELECT h.h_id, h.h_name, h.h_ortschaft, COUNT(DISTINCT w_id) AS l_anzahl_waren
FROM haendler h
LEFT JOIN liefert l ON (h.h_id = l.h_id)
GROUP BY h.h_id, h.h_name, h.h_ortschaft
ORDER BY h_id
;
-- mit korrelierter Subquery in der SELECT-Klausel
SELECT h_id, h_name, h_ortschaft,
(SELECT COUNT(DISTINCT w_id)
FROM liefert l
WHERE l.h_id = h.h_id
) AS l_anzahl_waren
FROM haendler h
;
-- mit LATERAL-JOIN
SELECT h_id, h_name, h_ortschaft, l_anzahl_waren
FROM haendler h, -- CROSS JOIN
LATERAL (SELECT COUNT(DISTINCT w_id)
FROM liefert l
WHERE l.h_id = h.h_id
) AS l(l_anzahl_waren)
;
Temporäre View
-- Nicht-korrelierte Subqueries können in eine temporäre View ausgelagert werden.
-- Temporäre Views sind vor allem dann wichtig, wenn eine Subquery mehrfach
-- in eine komplexen Anfrage verwendet wird.
WITH anzahl_alle_waren(l_anzahl_alle_waren)
AS -- MATERIALIZED
-- Subquery wird ausgewertet und temporär gespeichert (ohne Indexe!)
(SELECT COUNT(w_id) FROM ware)
SELECT h_id,
(SELECT COUNT(DISTINCT w_id) -- korrelierte Subquery
FROM liefert l
WHERE l.h_id = h.h_id -- Zugriff auf h.h_id,
) AS h_anzahl_waren, -- außerhalb der Subquery definiert
l_anzahl_alle_waren
FROM haendler h,
anzahl_alle_waren a -- CROSS JOIN OK, da a nur ein Tupel enthält
;
WITH anzahl_alle_waren(l_anzahl_alle_waren)
AS NOT MATERIALIZED -- Subquery wird nicht ausgewertet, sondern in Query eingefügt
-- => evtl. mehrfache Auswertung, aber Optimierung und
-- Indexausnutzung
(SELECT COUNT(w_id) FROM ware)
SELECT h_id,
(SELECT COUNT(DISTINCT w_id) -- korrelierte Subquery
FROM liefert l
WHERE l.h_id = h.h_id -- Zugriff auf h.h_id,
) AS l_anzahl_waren, -- außerhalb der Subquery definiert
l_anzahl_alle_waren
FROM haendler h,
anzahl_alle_waren a -- CROSS JOIN OK, da a nur ein Tupel enthält
;
-- Diese Version (mit NOT MATERIALIZED) entspricht wird in folgende
-- Anfrage übersetzt:
SELECT h_id,
(SELECT COUNT(DISTINCT w_id) -- korrelierte Subquery
FROM liefert l
WHERE l.h_id = h.h_id -- Zugriff auf h.h_id,
) AS l_anzahl_waren, -- außerhalb der Subquery definiert
FROM haendler h,
(SELECT COUNT(w_id) FROM ware) a -- CROSS JOIN OK, da a nur ein Tupel enthält
;
Subqueries in WHERE- und HAVING-Klauseln
/**************************************************************************
* Das IN-Prädikat:
* Welcher Händler liefert den Artikel mit der Warennumer 3?
**************************************************************************/
-- als Semin-Join (DISTINCT ist notwendig!)
SELECT DISTINCT h.h_id, h.h_name
FROM haendler h, liefert l
WHERE h.h_id = l.h_id AND l.w_id = 3
;
SELECT DISTINCT h.h_id, h.h_name
FROM haendler h JOIN liefert l USING (h_id)
WHERE l.w_id = 3
;
-- mit nicht-korrelierter Subquery (beste Lösung)
SELECT h.h_id, h.h_name
FROM haendler h
WHERE h.h_id IN
(SELECT l.h_id
FROM liefert l
WHERE l.w_id = 3
)
;
-- weniger performant, da korrelierte Subquery
SELECT h.h_id, h.h_name
FROM haendler h
WHERE 3 IN
(SELECT l.w_id
FROM liefert l
WHERE l.h_id = h.h_id
)
;
-- auch nicht besser
SELECT h.h_id, h.h_name
FROM haendler h
WHERE EXISTS
(SELECT *
FROM liefert l
WHERE l.h_id = h.h_id AND l.w_id = 3
)
;
Weitere Beispiel für das IN-Prädikat
/***************************************************************************
* explizite Aufzählung einer Menge
****************************************************************************/
SELECT *
FROM ware
WHERE w_typ IN ('CPU', 'RAM', 'Sonstiges')
;
SELECT *
FROM ware
WHERE w_typ = 'CPU' OR w_typ = 'RAM' OR w_typ = 'Sonstiges'
;
/***************************************************************************
* Das In-Prädikat mit Tupeln.
****************************************************************************/
SELECT ROW ('Maier', 'Königsbrunn')
IN
(SELECT h_name, h_ortschaft FROM haendler) AS ergebnis
;
Falsche Duplikate
/***************************************************************************
* Falsche Duplikate vermeiden!
* Wie lauten die Namen der Händler, die CPUs liefern?
****************************************************************************/
-- zu viele Tupel (falsche Duplikate)
SELECT h_name
FROM haendler JOIN liefert USING (h_id)
JOIN ware USING (w_id)
WHERE w_typ = 'CPU'
;
-- zu wenige Tupel (richtige Duplikate fehlen)
SELECT DISTINCT h_name
FROM haendler JOIN liefert USING (h_id)
JOIN ware USING (w_id)
WHERE w_typ = 'CPU'
;
-- richtige Antwort (Duplikate, aber keine falschen Duplikate)
SELECT h_name
FROM haendler
WHERE h_id
IN
(SELECT h_id
FROM liefert l JOIN ware w ON l.w_id = w.w_id
WHERE w_typ = 'CPU'
)
;
-- Es funktioniert auch mit einem Join; aber ohne Subquery geht es
-- immer noch nicht.
SELECT h_name
FROM (SELECT DISTINCT h_name, h_id
FROM haendler JOIN liefert USING (h_id)
JOIN ware USING (w_id)
WHERE w_typ = 'CPU'
) AS dummy
;
-- Am elegantesten ist die Lösung mit dem
-- DISTINCT-ON-Operator, der bislang
-- allerdings nur von PostgreSQL angeboten wird.
SELECT DISTINCT ON (h_id) h_name
FROM haendler JOIN liefert USING (h_id)
JOIN ware USING (w_id)
WHERE w_typ = 'CPU'
;
Divisionsoperator: Beispiel
-- Ausgangslage
-- Tabelle r:
SELECT DISTINCT h_id, w_id
FROM liefert
;
-- Tabelle s:
SELECT w_id
FROM ware
;
-- a = {hid, wid}\{w_id} = {h_id}
-- Alle Händler, die Waren liefern:
-- πa(r)
SELECT DISTINCT h_id
FROM liefert
;
-- Was wäre, wenn jeder Händler alles liefern würde?
-- πa(r)✕s
SELECT h_id, w_id
FROM (SELECT DISTINCT h_id
FROM liefert
) AS alle_haendler
CROSS JOIN
ware
;
-- Welche Händler liefern welche Waren NICHT (EXCEPT)?
-- (πa(r)✕s)\r
SELECT h_id, w_id
FROM (SELECT DISTINCT h_id
FROM liefert
) AS alle_haendler
CROSS JOIN
ware
EXCEPT
SELECT h_id, w_id
FROM liefert
;
Divisionsoperator: Beispiel (Fortsetzung)
-- Welche Händler liefern nicht alle Waren?
-- πa((πa(r)✕s)\r)
SELECT
DISTINCT h_id
FROM (SELECT h_id, w_id
FROM (SELECT DISTINCT h_id
FROM liefert
) AS alle_haendler
CROSS JOIN
ware
EXCEPT
SELECT h_id, w_id
FROM liefert
) AS wer_liefert_was_nicht
;
-- Welche Händler liefern alle Waren?
-- r ÷ s = πa(r) \ πa((πa(r)✕s)\r)
SELECT DISTINCT h_id
FROM liefert
EXCEPT
SELECT DISTINCT h_id
FROM (SELECT h_id, w_id
FROM (SELECT DISTINCT h_id
FROM liefert
) AS alle_haendler
CROSS JOIN
ware
EXCEPT
SELECT h_id, w_id
FROM liefert
) AS wer_liefert_was_nicht
;
-- Oder (effizienter) mit zusätzlicher Benutzung der Händler-Tabelle.
-- Aber immer noch ineffizient wegen CROSS JOIN.
SELECT h_id
FROM haendler
EXCEPT
SELECT h_id
FROM (SELECT h_id, w_id
FROM (SELECT h_id
FROM haendler
) AS alle_haendler
CROSS JOIN
ware
EXCEPT
SELECT h_id, w_id
FROM liefert
) AS wer_liefert_was_nicht
;
Für-Alle-Anfragen: Ersatzlösung mit EXISTS
SELECT DISTINCT l1.h_id
FROM liefert l1
WHERE NOT EXISTS -- Es gibt keine
(SELECT w_id
FROM ware -- Ware,
EXCEPT -- die der Händler l1.h_id nicht liefert
SELECT l2.w_id
FROM liefert l2 -- Diese Waren liefert er
WHERE l2.h_id = l1.h_id -- korrelierte Subquery
)
;
-- Oder noch performanter (da keine Duplikate entfernt werden müssen) .
SELECT h.h_id
FROM haendler h
WHERE NOT EXISTS
(SELECT w_id
FROM ware
EXCEPT
SELECT l.w_id
FROM liefert l
WHERE l.h_id = h.h_id
)
;
-- Alternative mit zweimal NOT EXISTS (doppelte Korrelation: sehr schlecht!)
SELECT h.h_id
FROM haendler h
WHERE NOT EXISTS
(SELECT *
FROM ware w
WHERE NOT EXISTS
(SELECT *
FROM liefert l
WHERE l.h_id = h.h_id
AND l.w_id = w.w_id
)
)
;
Für-Alle-Anfragen: Ersatzlösung mit Zählen
-- Alternativen mit Zählen (auch sehr effizient)
SELECT l.h_id, COUNT(DISTINCT w_id) AS anzahl -- anzahl war nicht gefragt
FROM liefert l
GROUP BY l.h_id
HAVING COUNT(DISTINCT w_id)
= (SELECT COUNT(*) FROM ware)
;
SELECT h_id
FROM (SELECT h_id, COUNT(DISTINCT w_id) AS l_anzahl_waren
FROM liefert l
GROUP BY h_id
) l
WHERE (SELECT COUNT(*) FROM ware) = l_anzahl_waren
;
SELECT h_id
FROM (SELECT h_id, COUNT(DISTINCT w_id) AS l_anzahl_waren
FROM liefert l
GROUP BY h_id
) l, -- CROSS JOIN: Kein Problem, da w nur ein Tupel enthält
(SELECT COUNT(*) AS w_anzahl_alle_waren
FROM ware
) w
WHERE l_anzahl_waren = w_anzahl_alle_waren
;
Min-Max-Anfragen
-- Alternativen mit Zählen (auch sehr effizient)
SELECT l.h_id, COUNT(DISTINCT w_id) AS anzahl -- anzahl war nicht gefragt
FROM liefert l
GROUP BY l.h_id
HAVING COUNT(DISTINCT w_id)
= (SELECT COUNT(*) FROM ware)
;
SELECT h_id
FROM (SELECT h_id, COUNT(DISTINCT w_id) AS l_anzahl_waren
FROM liefert l
GROUP BY h_id
) l
WHERE (SELECT COUNT(*) FROM ware) = l_anzahl_waren
;
SELECT h_id
FROM (SELECT h_id, COUNT(DISTINCT w_id) AS l_anzahl_waren
FROM liefert l
GROUP BY h_id
) l, -- CROSS JOIN: Kein Problem, da w nur ein Tupel enthält
(SELECT COUNT(*) AS w_anzahl_alle_waren
FROM ware
) w
WHERE l_anzahl_waren = w_anzahl_alle_waren
;
Echt kleiner an Stelle von kleiner gleich
/***************************************************************************
* Welche Waren liefert Maier aus Königsbrunn ECHT billiger
* als seine Kollegen?
* Beachten Sie, die Eieruhr entfällt, da Händler 2 sie zum selben Preis
* liefert wie Maier aus Königsbrunn.
*
* Den Speicherriegel (RAM) liefert nur Maier. Daher ist er auch
* billiger als seine Kollegen.
***************************************************************************/
SELECT wm.w_id, wm.w_typ, wm.w_bezeichnung, lm.l_preis, lm.l_lieferzeit
FROM haendler hm
JOIN liefert lm USING (h_id)
JOIN ware wm USING (w_id)
WHERE h_name = 'Maier' AND h_ortschaft = 'Königsbrunn' AND
l_preis < ALL (SELECT l_preis
FROM liefert la
WHERE la.w_id = lm.w_id AND la.h_id <> lm.h_id
)
;
Verwendung von MIN ist problematisch
-- Bei folgender Lösung wird der Speicherriegel nicht angezeigt,
-- da dieser Artikel nur von Maier aus Königsbrunn geliefert wird.
-- Daher kann für die anderen Händler kein Minimum ermittelt werden.
SELECT wm.w_id, wm.w_typ, wm.w_bezeichnung, lm.l_preis, lm.l_lieferzeit
FROM haendler hm
JOIN liefert lm USING (h_id)
JOIN ware wm USING (w_id)
WHERE h_name = 'Maier' AND h_ortschaft = 'Königsbrunn' AND
l_preis < (SELECT MIN(l_preis)
FROM liefert la
WHERE la.w_id = lm.w_id AND la.h_id <> lm.h_id
)
;
-- Workaround 1: Verwende < ALL anstelle von < (SELELCT MIN(...) ...)
-- (siehe oben)
-- Workaround 2: explizite Überprüfung,
-- ob andere Händler diese Ware liefern (hässlich)
SELECT wm.w_id, wm.w_typ, wm.w_bezeichnung, lm.l_preis, lm.l_lieferzeit
FROM haendler hm
JOIN liefert lm USING (h_id)
JOIN ware wm USING (w_id)
WHERE h_name = 'Maier' AND h_ortschaft = 'Königsbrunn' AND
(l_preis < (SELECT MIN(l_preis)
FROM liefert la
WHERE la.w_id = lm.w_id AND la.h_id <> lm.h_id
)
OR
(SELECT COUNT(*)
FROM liefert la
WHERE la.w_id = lm.w_id AND la.h_id <> lm.h_id
)
= 0
)
;
-- Workaround 3: einen künstlichen sündhaft teuren Preis einführen
SELECT wm.w_id, wm.w_typ, wm.w_bezeichnung, lm.l_preis, lm.l_lieferzeit
FROM haendler hm
JOIN liefert lm USING (h_id)
JOIN ware wm USING (w_id)
WHERE h_name = 'Maier' AND h_ortschaft = 'Königsbrunn' AND
l_preis < (SELECT MIN(l_preis)
FROM (SELECT h_id, w_id, l_preis FROM liefert
UNION
SELECT -1, lm.w_id, 999999.99 -- -1 sehr teurer Dummy-Händler
) la
WHERE la.w_id = lm.w_id AND la.h_id <> lm.h_id
)
;
Weitere Beispiele
SELECT w_id
FROM liefert lm
WHERE h_id = 1 AND
l_preis <= ALL (SELECT l_preis
FROM liefert la
WHERE la.w_id = lm.w_id
)
;
SELECT w_id
FROM liefert lm
WHERE h_id = 1 AND
l_preis = (SELECT MIN(l_preis)
FROM liefert la
WHERE la.w_id = lm.w_id
)
;
SELECT w_id
FROM liefert lm,
LATERAL (SELECT MIN(l_preis) AS min_preis
FROM liefert la
WHERE la.w_id = lm.w_id
) dummy
WHERE h_id = 1 AND l_preis = min_preis
;
SELECT w_id
FROM liefert lm
JOIN LATERAL (SELECT MIN(l_preis) AS min_preis
FROM liefert la
WHERE la.w_id = lm.w_id
) dummy
ON (l_preis = min_preis)
WHERE h_id = 1
;
SELECT w_id -- Alle Waren von Maier
FROM liefert
WHERE h_id = 1
EXCEPT -- mit Ausnahme derer,
SELECT w_id -- die andere billiger liefern
FROM liefert lm
WHERE h_id = 1 AND
EXISTS
(SELECT *
FROM liefert la
WHERE la.w_id = lm.w_id AND
la.l_preis < lm.l_preis
)
;
-- Kürzer mit NOT EXISTS
SELECT w_id
FROM liefert lm
WHERE h_id = 1 AND
NOT EXISTS
(SELECT *
FROM liefert la
WHERE la.w_id = lm.w_id AND
la.l_preis < lm.l_preis
)
;
Die meisten …
-- Wie viele unterschiedliche Waren liefern die einzelnen Händler?
SELECT h_id, COUNT(DISTINCT w_id) AS l_anzahl_waren
FROM liefert
GROUP BY h_id
;
-- Wer liefert die meisten unterschiedlichen Waren?
SELECT h_id, COUNT(DISTINCT w_id) AS l_anzahl_waren
FROM liefert
GROUP BY h_id
HAVING COUNT(DISTINCT w_id) =
(SELECT MAX(anzahl)
FROM (SELECT COUNT(DISTINCT w_id) AS anzahl
FROM liefert
GROUP BY h_id
) dummy
)
;
-- Dasselbe noch einmal DRY!
WITH anzahl_waren_je_haendler(h_id, l_anzahl_waren)
AS
(SELECT h_id, COUNT(DISTINCT w_id)
FROM liefert
GROUP BY h_id
)
SELECT h_id, l_anzahl_waren
FROM anzahl_waren_je_haendler
WHERE l_anzahl_waren =
(SELECT MAX(l_anzahl_waren)
FROM anzahl_waren_je_haendler
)
;
Geschachtelte Aggregation mit Windowing
-- Maximale Anzahl der Waren, die irgendein Händler liefert.
SELECT MAX(COUNT(DISTINCT w_id)) OVER () AS anzahl
FROM liefert
GROUP BY h_id
; -- Beachten Sie: Alle Werte sind mit Sicherheit gleich.
SELECT MAX(COUNT(DISTINCT w_id)) OVER () AS anzahl
FROM liefert
GROUP BY h_id
FETCH FIRST 1 ROW ONLY -- OK, da alle Werte mit Sicherheit gleich sind wg. OVER ()
;
SELECT DISTINCT MAX(COUNT(DISTINCT w_id)) OVER () AS anzahl
FROM liefert
GROUP BY h_id
;
-- Damit kann man die Frage auch beantworten.
SELECT h_id, COUNT(DISTINCT w_id) AS l_anzahl_waren
FROM liefert
GROUP BY h_id
HAVING COUNT(DISTINCT w_id) =
(SELECT DISTINCT MAX(COUNT(DISTINCT w_id)) OVER () AS anzahl
FROM liefert
GROUP BY h_id
)
;
-- Ohne DISTINCT, mit =SOME
SELECT h_id, COUNT(DISTINCT w_id) AS l_anzahl_waren
FROM liefert
GROUP BY h_id
HAVING COUNT(DISTINCT w_id) =SOME
(SELECT MAX(COUNT(DISTINCT w_id)) OVER () AS anzahl
FROM liefert
GROUP BY h_id
)
;
-- Ohne DISTINCT, mit =SOME und View
SELECT h_id, l_anzahl_waren
FROM anzahl_waren_je_haendler
WHERE l_anzahl_waren =SOME
(SELECT MAX(COUNT(DISTINCT w_id)) OVER () AS anzahl
FROM liefert
GROUP BY h_id
)
;
