Das Ziel ist es, den Preis für die nächste Zahl des m3 und das Gewicht zu ermitteln und in eine Tabelle einzutragen.
Dieses Beispiel funktioniert für mich. Aber ich hart Code m3, weight, countryundtransportername
Abfrage1:
SELECT
country,
transportername,
ABS(m3-0.5) as ABS_M3 ,
m3,
ABS(weight-5) as ABS_weight,
weight,
price
FROM database.transportcost
WHERE m3 >= 0.5 AND
weight >= 5 AND
country = "GB" AND
transportername like '%aa%'
Group by transportername
Order by ABS(m3-0.5) +
ABS(weight-5) +
price
Jetzt möchte ich den Hardcode-Wert aus einer anderen Tabelle erhalten. Ich weiß nicht, welchen Wert ich setzen muss ~~xx.volumen~~und ~~xx.gewicht~~.
Abfrage2:
INSERT INTO database.transportcostPerItem (sku,country,transportname,weight,m3,price)
SELECT g.orderitemid, p2.country, p2.transportername, p2.weight, p2.m3, p2.price
FROM database.orderitem g
JOIN database.order i on i.orderid = g.orderid
JOIN database.matrixtable xx on g.orderitemid = xx.sku
JOIN database.vlog cc on i.orderid = cc.orderid
JOIN
(
SELECT *
FROM database.transportcost
Group by transportername
Order by ABS(m3-~~xx.volumen~~) +
ABS(weight-~~xx.gewicht~~) +
price
) as p2 on p2.country = i._country AND
p2.transportername = cc._transportername AND
p2.weight = xx.gewicht AND
p2.m3 = xx.volumen
;
Bearbeiten:
Beispieldaten
Tabelle: database.transportcost country | Transportername | m3 | Gewicht | Preis -------- + ----------------- + -------- + -------- + ---- ---- GB | aa | 50,00 | 50 | 77,70 GB | bb | 0,50 | 125 | 83,19 GB | cc | 0,50 | 125 | 96,03 GB | bb | 0,60 | 150 | 83,19 GB | aa | 75,00 | 75 | 89,04 GB | cc | 0,60 | 150 | 96,03 GB | dd | 50,00 | 50 | 163,38 GB | cc | 0,70 | 175 | 96,03 GB | bb | 0,70 | 175 | 96,85 GB | ee | 0,53 | 175 | 102,78 GB | gg | 0,53 | 175 | 110,37 GB | aa | 100,00 | 100 | 89,04 GB | cc | 0,80 | 200 | 96,03 GB | bb | 0,80 | 200 | 96,85 GB | ff | 0,60 | 200 | 110,33 DE | aaa | 0,50 | 50 | 26.40 DE | bbb | 0,50 | 75 | 31,84 DE | aaa | 0,75 | 75 | 34.19 DE | ccc | 0,50 | 100 | 34.17 DE | bbb | 0,67 | 100 | 35,47 DE | ccc | 1,00 | 100 | 37,59 DE | ddd | 0,50 | 100 | 62,38 DE | ccc | 0,63 | 125 | 43.04 OF | bbb | 0,83 | 125 | 44.24 DE | aaa | 0,93 | 125 | 45,84 OF | eee | 0,50 | 125 | 53,80 VON | fff | 0,50 | 125 | 54.02
Query 1 Ergebnisse für das Land GB
Land | Transportername | ABS_M3 | m3 | ABS_gewicht | Gewicht | Preis -------- + ----------------- + ---------------------- - + ------- + ------------ + -------- + ------- GB | aa | 49,5 | 50,00 | 45 | 50 | 77,70 GB | bb | 0 | 0,50 | 120 | 125 | 83,19 GB | cc | 0 | 0,50 | 120 | 125 | 96,03 GB | dd | 49,5 | 50,00 | 45 | 50 | 163,38 GB | ee | 0.030000000000000027 | 0,53 | 170 | 175 | 102,78 GB | gg | 0.030000000000000027 | 0,53 | 170 | 175 | 110,37
Query 1 Ergebnisse für das Land DE
Land | Transportername | ABS_M3 | m3 | ABS_gewicht | Gewicht | Preis -------- + ----------------- + -------- + ------ + ------ ------ + -------- + ------- DE | aaa | 0 | 0,50 | 45 | 50 | 26.40 DE | bbb | 0 | 0,50 | 70 | 75 | 31,84 DE | ccc | 0 | 0,50 | 95 | 100 | 34.17 DE | ddd | 0 | 0,50 | 95 | 100 | 62,38 DE | eee | 0 | 0,50 | 120 | 125 | 53,80 DE | fff | 0 | 0,50 | 120 | 125 | 54.02 DE | ggg | 0 | 0,50 | 195 | 200 | 87,29
Die Ergebnisse am Ende Query 2sollten folgendermaßen aussehen:
Tabelle: database.transportcostPerItem orderitemid | Land | Transportername | m3 | Gewicht | Preis ------------ + --------- + ----------------- + ----- + - ------ + ------ 1 | GB | aa | 0,5 | 5 | 77,70 2 | DE | aaa | 0,5 | 5 | 26.40
Es scheint, dass Sie nur nach dem günstigsten Transport für gegebenes m3 und Gewicht suchen. In einem ersten Schritt finden Sie alle Transporte für mindestens den angegebenen m3 und das angegebene Gewicht. Von diesen wählen Sie dann den niedrigsten Preis.
select *
from transportcost
where country = 'GB'
and m3 >= 0.5
and weight >= 5
order by price
limit 1;
Wenn Sie stattdessen die Maße wünschen, die Ihrem angegebenen m3 und Gewicht am nächsten kommen, selbst wenn dieser Transport teurer wäre, können Sie nach der Summe der Prozentsätze bestellen:
select *
from transportcost
where country = 'GB'
and m3 >= 0.5
and weight >= 5
order by m3 / 0.5 + weight / 5, price
limit 1;
Anstelle von m3, Gewicht, Land und Transporternamen erhalten Sie jetzt die Werte aus einer Bestelldetailtabelle. Wenn dies nur ein einziger Datensatz mit Bestelldetails wäre, wäre der Ansatz genau der gleiche, nur anstatt mit festen Werten zu vergleichen ( m3 >= 0.5), würden Sie ihn mit den Werten des Bestelldetailsatzes vergleichen ( transportcost.m3 >= orderdetail.m3). Leider funktioniert dieser Ansatz bei mehreren Bestelldetailsätzen nicht mehr, da wir die Ergebnisse nicht auf eine Zeile beschränken können, sondern stattdessen eine Zeile pro Bestelldetailsatz benötigen. Dies würde mit Fensterfunktionen gelöst werden ( ROW_NUMBER, RANKetc.) oder Seiten verbindet ( CROSS APPLY) in SQL - Standard. MySQL bietet keine.
So fügen Sie sich der Transportkostentabelle an:
select *
from <your order tables>
join transportcost tc
where tc.country = i._country
and tc._transportername = cc._transportername
and tc.m3 >= xx.volumen
and tc.weight >= xx.gewicht;
Und jetzt müssen Sie einen Weg finden, Ihre Ergebnisse zu bewerten, um nur die beste Übereinstimmung pro Bestelldetail zu erzielen. Eine Möglichkeit besteht darin, ROW_NUMBERmit Variablen zu emulieren . Vielleicht möchten Sie dies in anderen Antworten nachschlagen. Eine andere wäre eine Limit-Unterabfrage in der SELECT clause, aber damit dies funktioniert, benötigen Sie eine einzelne Spalte, die einen Datensatz in der Transportkostentabelle identifiziert. Angenommen, Sie fügen eine Spalte mit dem Namen hinzu IDund füllen sie mit eindeutigen Werten ...
select <some order columns>, tc.*
from
(
select <some order columns>,
(
select id
from transportcost tc
where tc.country = i._country
and tc._transportername = cc._transportername
and tc.m3 >= xx.volumen
and tc.weight >= xx.gewicht
order by tc.m3 / xx.volumen + tc.weight / xx.gewicht, tc.price
limit 1
) as best_transportcost_id
from <your order tables>
) data
join transportcost tc
where tc.id = data.best_transportcost_id;
(Sie könnten dies irgendwie ohne eine ID tun, indem Sie die Werte verketten, z. B. 'GB-aa-50.00-50-77.70', um eine Zeichenfolge zu erhalten, die einen Transportkostendatensatz identifiziert, aber ich empfehle dies nicht. Es ist besser habe eine eindeutige ID mit einem Index für schnelle Suchvorgänge. Und noch besser wäre es, mit einem besseren DBMS zu arbeiten :-)
Dieser Artikel stammt aus dem Internet. Bitte geben Sie beim Nachdruck die Quelle an.
Bei Verstößen wenden Sie sich bitte [email protected] Löschen.
Lass mich ein paar Worte sagen