www.mikrocontroller.net

Forum: PC-Programmierung Hilfe: Komplizierte SQL-Abfrage mit Staffelpreisen


Autor: Gerhard (Gast)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Hallo Leute,

ich bastel mir gerade ein paar Abfragen für meine selbstgestrickte 
Bauteileverwaltung und komme an einem Punkt einfach nicht weiter.

Und zwar habe ich in einer Tabelle die Bezugsquellen für ein Teil:

int Teile_ID
int Lieferant_ID
varchar Bestell_Bezeichnung
int Preis_ID
date Zuletzt_Aktualisiert

Die Tabelle mit den Staffelpreisen ist wie folgt aufgebaut:

int ID
int Stueckzahl
float Preis

Jetzt kann ich natürlich über die Verknüpfung 
Bezugsquelle.Preis_ID->Staffelpreis.ID für eine Stückzahl einen Preis 
heraussuchen. Das schaffe ich aber nicht in SQL selbst, sondern das muss 
ich in meiner PHP-Logik in einer Schleife selbst machen, in der getestet 
wird, zwischen welchen Staffelpreisen die gewünschte Stückzahl liegt.

Was ich gerne hätte, wäre eine SQL-Abfrage, mit der ich direkt den 
Gesamtpreis, nicht den Einzelpreis, für eine bestimmte Stückzahl in der 
Abfrage herausbekomme, d.h. ich brauche nicht nur eine Multiplikation, 
sondern auch eine Entscheidung... Zurück hätte ich auch gerne die zu 
bestellende Stückzahl, falls es eine Mindestbestellmenge gibt oder falls 
es günstiger ist, 50 Stück zu bestellen als 49.

Als Beispiel gibt es eine Preisstaffelung wie folgt:

ab 10 Stück: 3,99 €
ab 50 Stück: 3,15 €

Das hieße, das man niemals 49 Stück bestellen sollte, und für jede 
Stückzahlabfrage unterhalb von 10 sollte der Preis für 10 Stück 
zurückgegeben werden (Mindestbestellmenge).

Autor: hp-freund (Gast)
Datum:

Bewertung
0 lesenswert
nicht lesenswert

Autor: Vlad Tepesch (vlad_tepesch)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Das Datenbank-Design ist ungünstig.

Jeder Artikel hat genau einen Preis.
Eigentlich macht es so noch nicht einmal sinn, die Preise in eine eigene 
Tabelle auszulagern, weil man sicher nicht von mehreren Artikeln auf 
einen Preiseintrag verlinken möchte.

ein und der selbe Artikel kann jedoch unterschiedliche Staffelpreise 
haben.

der eintrag Preis_ID sollte also weg und stattdessen in die Tabelle 
Preis eine Bezugsquelle ID.

dann kann man für einen Artikel nämlich Tatsächlich Staffelpreise 
erzeugen.
Gerhard schrieb:
> Zurück hätte ich auch gerne die zu
> bestellende Stückzahl, falls es eine Mindestbestellmenge gibt oder falls
> es günstiger ist, 50 Stück zu bestellen als 49.

Sprich: mit dem Derzeitigem Schema geht es gar nicht.


Gerhard schrieb:
> Jetzt kann ich natürlich über die Verknüpfung
> Bezugsquelle.Preis_ID->Staffelpreis.ID für eine Stückzahl einen Preis
> heraussuchen. Das schaffe ich aber nicht in SQL selbst, sondern das muss
> ich in meiner PHP-Logik in einer Schleife selbst machen, in der getestet
> wird, zwischen welchen Staffelpreisen die gewünschte Stückzahl liegt.

So wie es aussieht fehlt es dir aber an absoluten Grundkenntnissen in 
SQL, die du dir sowiso aneignen solltenst, wenn du sowas vor hast.

Die Abfrage ist simple:

SELECT * FROM Bezugsquellen as b, Preise as p WHERE pr.ID = b.preis_ID;

dies gibt dir eine Liste aller Bezugsquellen, die auch einen Eintrag in 
Preise haben.

Eine Multiplikation ist einfach:
SELECT Preis, Stueckzahl,  (Preis * Stuckzahl) AS Gesamtpreis FROM 
Preise;

Must du halt mit einer entsprechenden Abfrage kombinieren



Ausßerdem hat die Tabelle Bezugsquellen scheinbar keine ID.

Autor: Gerhard (Gast)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Moin Vlad,

danke für deinen hilfreichen Beitrag. Ich habe kein angeborenes Talent 
für SQL und mich nur genau so weit eingelesen, wie ich unbedingt musste, 
um meine Bauteileverwaltung auf die Beine zu stellen.

> Jeder Artikel hat genau einen Preis.

Jeder Artikel hat einen (Staffel-) Preis pro Bezugsquelle, ja.

> Eigentlich macht es so noch nicht einmal sinn, die Preise in eine eigene
> Tabelle auszulagern, weil man sicher nicht von mehreren Artikeln auf
> einen Preiseintrag verlinken möchte.

Ok, ich kannte keinen besseren Weg, diese 1:n-Beziehung darzustellen, 
denn ein Staffelpreis sind ja mehrere Preise... Hm.

> der eintrag Preis_ID sollte also weg und stattdessen in die Tabelle
> Preis eine Bezugsquelle ID. dann kann man für einen Artikel nämlich
> Tatsächlich Staffelpreise erzeugen.

Klingt logisch, wenn auch nicht intuitiv (für mich). Ich denke mal 
darüber nach.

> Eine Multiplikation ist einfach:
> SELECT Preis, Stueckzahl,  (Preis * Stuckzahl) AS Gesamtpreis FROM
> Preise;

Das ist mir klar, aber gibt es die Möglichkeit, in EINER Abfrage mit 
Artikel-ID und gewünschter Stückzahl die günstigste zu bestellende 
Anzahl und den Gesamtpreis zurückzugeben?

> Ausßerdem hat die Tabelle Bezugsquellen scheinbar keine ID.

Die brauche ich nirgendwo.

Autor: Vlad Tepesch (vlad_tepesch)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Gerhard schrieb:
>> Ausßerdem hat die Tabelle Bezugsquellen scheinbar keine ID.
>
> Die brauche ich nirgendwo.

egal, jede Tabelle braucht einen Primärschlüssel.

Gerhard schrieb:
> Ok, ich kannte keinen besseren Weg, diese 1:n-Beziehung darzustellen,
> denn ein Staffelpreis sind ja mehrere Preise... Hm.

du hast jetzt aber keine 1:n Beziehung.

wie willst du denn für einen Eintrag 2 Preise anlegen? -> geht nicht

Gerhard schrieb:
> Das ist mir klar, aber gibt es die Möglichkeit, in EINER Abfrage mit
> Artikel-ID und gewünschter Stückzahl die günstigste zu bestellende
> Anzahl und den Gesamtpreis zurückzugeben?

Aso

folgendes (Minimal-) Schema vorausgesetzt:

Table Bezugsquellen:
ID
Artikel_ID
(rest egal)

Table Preise:
ID
Bezugsquelle_ID
Stueckzahl
Einzelpreis
(rest egal)




Alle Bezugsquellen zu bestimmten Artikel(myArticle):
SELECT * FROM Bezugsquellen WHERE Artikel_ID = myArticle;


Alle Bezugsquellen und Preise zu bestimmten Artikel(myArticle)
SELECT *
FROM Bezugsquellen, Preise 
WHERE     Bezugsquellen.Artikel_ID = myArticle 
      AND Preise.Bezugsquelle_ID = Bezugsquellen.ID
;


Alle Preise zu diesen Bezugsquellen mit berechnetem Gesamtpreis zu 
bestimmter Anzahl (myCount):
SELECT *, (( Preise.Stueckzahl > myCount ? Preise.Stueckzahl: myCount) * Preise.Einzelpreis) AS Gesamtpreis
FROM Bezugsquellen, Preise 
WHERE     Bezugsquellen.Artikel_ID = myArticle 
      AND Preise.Bezugsquelle_ID = Bezugsquellen.ID
ORDER BY Gesamtpreis 
;
hier muss man natürlich scheuen, ob die Datenbank so eine Art binäre 
MAX-Funktion (die das größere der beiden Argumente zurückgibt) oder den 
ternären operator (a?b:c) unterstützt.
Ansonsten muss man schauen, wie man selbst Funktionen einbauen kann.

Aber ohne Garantie, hab ich mir jetzt so aus den Fingern gezogen, ohne 
es getestet zu haben.

Autor: der mechatroniker (Gast)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Tip: bei einigen SQL-Dialekten kann man auch ein CASE WHEN... ELSE... 
END als ternären Operator missbrauchen.

Autor: Gerhard (Gast)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Hallo Vlad,

vielen Dank für deine Unterstützung. Ich habe meine Datenbankstruktur 
nicht geändert, die Abfrage aber trotzdem hinbekommen. Momentan sieht es 
so aus:
SELECT
  supplier.name,
  component_source.order_code,
  IF (price.quantity > $qty, price.quantity, $qty) AS order_quantity,
  price.price * IF (price.quantity > $qty, price.quantity, $qty)
    AS price_total
FROM
  component_source,
  supplier,
  price
WHERE
  component_id = $partno AND
  component_source.supplier_id = supplier.id AND
  component_source.price_id = price.id
ORDER BY
  price_total ASC
LIMIT
  1;

Als Eingabe habe ich dann nur $partno=798 und $qty=20 und bekomme 
folgende Ausgabe:
+---------+---------------+----------------+-----------------+
| name    | order_code    | order_quantity | price_total     |
+---------+---------------+----------------+-----------------+
| Digikey | AD8310ARMZ-ND |             25 |          147.72 |
+---------+---------------+----------------+-----------------+

Die Tabelle price sieht so aus:
mysql> select * from price where id=2;
+----+----------+---------+
| id | quantity | price   |
+----+----------+---------+
|  2 |        1 | 7.39000 |
|  2 |       25 | 5.90880 |
|  2 |      100 | 4.98540 |
|  2 |      250 | 4.80080 |
|  2 |      500 | 4.69000 |
|  2 |     1000 | 4.61615 |
+----+----------+---------+
6 rows in set (0.00 sec)

Damit bin ich erstmal zufrieden.

Was ich später noch implementieren muss, ist die Berücksichtigung von 
Verpackungseinheiten, so dass beispielsweise auch Reels bestellt werden 
können - die haben dann die gleiche Teilenummer, aber eine andere 
Bestellbezeichnung.

Autor: Vlad Tepesch (vlad_tepesch)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Gerhard schrieb:
> vielen Dank für deine Unterstützung. Ich habe meine Datenbankstruktur
> nicht geändert, die Abfrage aber trotzdem hinbekommen. Momentan sieht es
> so aus:

hm, ok.

Das was bei dir ID heißt ist scheinbar so was ähnliches, was ich 
Bezugsquelle_ID genannt habe, dann habe ich deine Namen 
misinterpretiert, was ein Hinweis darauf ist, dass diese ungünstig 
gewählt sind.

Ich möchte nur nochmal betonen, dass jede Tabelle ein Primärschlüssel 
haben sollte.
Üblicherweise wird dieser ID genannt und hat die Eigenschaften PRIMARY 
KEY, (damit impliit: NOT NULL, UNIQUE) sowie AUTOINCREMENT (oder 
vergleichbares des entsprechenden SQL-Dialektes)

deine component_source-Tabelle sollte bereits eine eindeutige spalte 
haben (ID) somit ist das component_source.preis_id überflüssig.
price.id sollte sich auf die component_source.ID beziehen und nicht auf 
ein seperates feld.

Das hat den Vorteil, das automatisch jede component_source eine 
eindeutige nummer hat und du dich nicht selbst darum kümmern muss, dass 
price_id eindeutig ist (es macht in meinen Augen keinen Sinn, dass 
mehrere Artikel den gleichen Staffelpreise haben - sollte das doch Sinn 
machen löst man das über Link-Tables)
momentan hast du die Aufgabe selbst eine Zahl zu finden, die weder in 
prices.id noch in component_source.price_id existiert.


Fremdschlüssel sollten als solche Erkennbar sein.
dein price.id würde ich also price.component_source_id oder so nennen

Autor: Andreas Schweigstill (Firma: Schweigstill IT) (schweigstill) Benutzerseite
Datum:

Bewertung
0 lesenswert
nicht lesenswert
Gerhard schrieb:
> Die Tabelle price sieht so aus:
> mysql> select * from price where id=2;
> +----+----------+---------+
> | id | quantity | price   |
> +----+----------+---------+
> |  2 |        1 | 7.39000 |
> |  2 |       25 | 5.90880 |
> |  2 |      100 | 4.98540 |
> |  2 |      250 | 4.80080 |
> |  2 |      500 | 4.69000 |
> |  2 |     1000 | 4.61615 |
> +----+----------+---------+
> 6 rows in set (0.00 sec)
>
> Damit bin ich erstmal zufrieden.

schauder Das ist ein wirklich schlechtes Datenbankschema. Wie von Vlad 
Tepesch schon geschrieben, gibt es keinen echten Primärschlüssel. 
Lediglich die Kombination mehrerer Felder (z.B. id, quantity) ist 
eindeutig und kann ggf. als Primärschlüssel dienen.

Das ganze ist aber viel zu fehlerträchtig. Es gibt hier überhaupt keine 
Notwendigkeit, mit zusammengesetzten Primärschlüsseln zu arbeiten.

Autor: Gerhard (Gast)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
So, überzeugt.

price.id heißt ab sofort price.component_source_id und ist ein 
Fremdschlüssel auf die Tabelle mit den Beschaffungsquellen, welche einen 
eigenen Primärschlüssel bekommt.

Das ganze System muß noch erweitert werden, und dabei wird es ziemlich 
kompliziert. So brauche ich noch eine separate Tabelle mit Teilenummern. 
Einer Teilenummer kann ich verschiedene Artikel zuordnen, die mechanisch 
und elektrisch äquivalent sind. So kann ich beispielsweise auch einen 
alten AT90S2313 durch einen Attiny-Nachfolger ersetzen, der 
abwärtskompatibel ist.

Dann wird es eine Tabelle "Baugruppen" geben, in denen Teilenummern über 
ihren Bezeichner ("R109", "IC507") mit der Baugruppen-ID verknüpft sind. 
Das soll dann ermöglichen, Kleinserien von bestimmten Platinen möglichst 
wirtschaftlich zu planen. Ich kann dann dem Kunden ziemlich genaue 
Angebote machen und vor allem auch erkennen, ab welchen Stückzahlen sich 
signifikante Verbesserungen im Einzelpreis ergeben.

Ob ich später die Versandkosten und Mindestbestellmengen von einzelnen 
Quellen erfasse, weiß ich noch nicht. Eine Optimierung wäre zwar 
denkbar, aber sicherlich nicht einfach zu programmieren:

"Lohnt es sich, das Teil etwas preiswerter bei B zu kaufen, wenn dies 
das einzige Teil ist, das bei B gekauft wird und x Euro Versandkosten 
hinzukommen?"

Autor: sebastians (Gast)
Datum:

Bewertung
0 lesenswert
nicht lesenswert
"Lohnt es sich, das Teil etwas preiswerter bei B zu kaufen, wenn dies
das einzige Teil ist, das bei B gekauft wird und x Euro Versandkosten
hinzukommen?"

Schritt 1 (Ist nur eine Optimierung, Schritt 2 geht dann schneller): 
alle Lieferanten, zu denen es keine Alternative gibt (weil sie ein Teil 
haben, das es nirgends sonst gibt): Hier musst du bestellen.

Schritt 2: Für alle anderen Lieferanten: Probier alle Kombinationen 
durch. Lieferant dabei, Lieferant nicht dabei... gibt 
2^Anzahl_Lieferanten Kombinationen. Sollte mit Brute Force machbar sein 
solange du nur ein paar Lieferanten hast (nicht viel mehr als 10, so um 
die 20 wirds kritisch...)
Kombinationen wo du ein Teil nicht bekommst fallen raus.
Nimm die Kombination, wo die Summe für Porto+Teile-Kosten minimal ist.

Antwort schreiben

Die Angabe einer E-Mail-Adresse ist freiwillig. Wenn Sie automatisch per E-Mail über Antworten auf Ihren Beitrag informiert werden möchten, melden Sie sich bitte an.

Wichtige Regeln - erst lesen, dann posten!

  • Groß- und Kleinschreibung verwenden
  • Längeren Sourcecode nicht im Text einfügen, sondern als Dateianhang

Formatierung (mehr Informationen...)

  • [c]C-Code[/c]
  • [avrasm]AVR-Assembler-Code[/avrasm]
  • [code]Code in anderen Sprachen, ASCII-Zeichnungen[/code]
  • [math]Formel in LaTeX-Syntax[/math]
  • [[Titel]] - Link zu Artikel
  • Verweis auf anderen Beitrag einfügen: Rechtsklick auf Beitragstitel,
    "Adresse kopieren", und in den Text einfügen




Bild automatisch verkleinern, falls nötig
Bitte das JPG-Format nur für Fotos und Scans verwenden!
Zeichnungen und Screenshots im PNG- oder
GIF-Format hochladen. Siehe Bildformate.
Hinweis: der ursprüngliche Beitrag ist mehr als 6 Monate alt.
Bitte hier nur auf die ursprüngliche Frage antworten,
für neue Fragen einen neuen Beitrag erstellen.

Mit dem Abschicken bestätigst du, die Nutzungsbedingungen anzuerkennen.