H2 SQL Zeilenwert passend zu anderem Zeilenwert

[@author]

Hallo,

ich habe folgende Tabelle:


Nun möchte ich daraus die Lieferanten selektieren, die mir für den jeweiligen Artikel den günstigsen Preis (Einkaufspreis) anbieten.

Habe nun folgendes SQL-Statement:

select artnr, count(artnr) as anzahl_lieferanten, bezeichnung, lagerbestand, bestellmenge, min(einkaufspreis) as bestpreis
from angebotsuebersicht
group by artnr
order by artnr

Ausgabe sieht wie folgt aus:


Mir fehlt jetzt noch der passende Lieferant (LNR) zum Bestpreis..

Ich habe schon alles mögliche versucht, leider verzweifle ich da gerade dran ???:L
Das kann doch nicht so schwer sein?!

Hoffe mir kann einer helfen

 

[ceving]

Durch "group by" verlierst du Informationen. Daher ist es einfacher das "group by" in einem Sub-Select zu machen, um die Ausgangstabelle zu filtern. Dabei bleibt deine Artikelnummer erhalten. Hier mal ein vereinfachtes Beispiel:

SQL Fiddle

create table artikel
(
  id integer,
  preis decimal(5,2)
);

insert into artikel values (1, 1.50);
insert into artikel values (1, 1.60);
insert into artikel values (1, 2.10);
insert into artikel values (2, 3.50);
insert into artikel values (2, 4.60);
insert into artikel values (2, 5.10);

select * from artikel a1 
where a1.preis = (select min(a2.preis) from artikel a2 where a1.id = a2.id group by id);

 

[@author]

Vielen Dank für diese hilfreiche Antwort!

Das hat auch 1 zu 1 bei meinem Beispiel funktioniert.
Allerdings verstehe ich das mit dem 2. Alias nicht so ganz.
Also man verwendet ihn einmal als Referenz um Bezug zur neuen Unterabfrage zu nehmen.
Aber wofür das a1.id = a2.id?!

Nochwas:
Bezieht sich dieses min(...) immer falls folgend auf das group by und sucht innerhalb einer Gruppe den kleinsten Wert?

 

[ceving]

Vielen Dank für diese hilfreiche Antwort!

Das hat auch 1 zu 1 bei meinem Beispiel funktioniert.
Allerdings verstehe ich das mit dem 2. Alias nicht so ganz.
Also man verwendet ihn einmal als Referenz um Bezug zur neuen Unterabfrage zu nehmen.
Aber wofür das a1.id = a2.id?!

Nochwas:
Bezieht sich dieses min(...) immer falls folgend auf das group by und sucht innerhalb einer Gruppe den kleinsten Wert?

Die Lösung, die ich beschrieben habe ist nicht besonders performant. Ich habe unten noch eine zweite wahrscheinlich bessere Lösung beschrieben.

Nichts desto trotz zur ersten Lösung: sie macht für jede Zeile in deiner Ausgangstabelle ein Sub-Select für die Minimum-Suche und dummer Weise auch redundante Sub-Selects. Es gibt von jedem Artikel drei verschiedene Preise und es wird für jeden der drei Preise einmal die Suche nach dem Preis-Minimum gemacht. Eigentlich würde es reichen das Minimum nur einmal zu bestimmen.

Die Suche nach dem Preis-Minimum im Sub-Select darf aber nur für alle identische Artikel erfolgen. Die Artikel-ID bei der Minimum-Suche (a2.id) muss also die gleiche sein wie die Artikel-ID des Artikels, der gerade betrachtet wird (a1.id). Deswegen a1.id = a2.id.

Die min()-Funktion bezieht sich auf alles, was die where-Bedingung auswählt. Das "group by" in meinem Beispiel ist eigentlich Unsinn, weil überflüssig. Du kannst es einfach weglassen.

Mit "group by" kann man manchmal geschicktere Abfragen machen aber ich habe auch so meine Schwierigkeiten dabei, "group by"-Abfragen im Kopf richtig sortiert zu kriegen.

Ich habe nochmal gegrübelt und bin zu der folgenden wahrscheinlich besseren Lösung gekommen.

Die Lösung filtert die Ausgangstabelle mit "group by" und lässt nur die Minimal-Preise übrig. Diese Tabelle wird dann mit deiner Ausgangstabelle ge"join"t. Wodurch die Bestandswerte erhalten bleiben. Die Relation für den Join ist die Kombination aus Artikel-ID und Artikel-Preis.

Beispiel: SQL Fiddle

create table artikel
(
  id integer,
  bestand integer,
  preis decimal(5,2)
);

insert into artikel values (1, 3, 1.50);
insert into artikel values (1, 5, 1.60);
insert into artikel values (1, 7, 2.10);
insert into artikel values (2, 4, 3.50);
insert into artikel values (2, 6, 4.60);
insert into artikel values (2, 8, 5.10);

select *
from artikel a1 join
(
  select id, min(preis) as preis
  from artikel
  group by id
) a2
using (id, preis);