mysql: Kompliziertes SQL-Problem

[Lukas.]

Hallo,

ich habe eine Query, die wie folgt beginnt.

SELECT 
sum(
(CASE 
WHEN (tab_b.MengeB = 0 AND tab_b.Art = 'AZ') THEN 0
WHEN (tab_b.MengeB = 0 AND tab_b.Art = 'AK') THEN 0
WHEN tab_b.MengeB = 0 THEN 1
ELSE
tab_b.MengeB
END)
* tab_b.PreisB *
...

Nun habe ich im System etwas umgestellt, was auch eine Umstellung in der Query zur Folge haben muß.

Und zwar müssen die Spalten "tab_b.MengeB" und "tab_b.PreisB" in obiger Query gegen die Spalten "tab_b.MengeA" sowie "tab_b.PreisA" ausgetauscht werden, wenn folgendes vorliegt:

In der Tabelle B (also tab_b) gibt es eine Spalte "VorID", in der die ID eines Vorgangs steht. In der Vorgangstabelle gibt es eine Spalte (A_B), die entweder ein "A" oder ein "B" enthält. Entsprechend muß dann in obiger Query "tab_b.MengeB" und "tab_b.PreisB" bzw. "tab_b.MengeA" und "tab_b.PreisA" eingesetzt werden. Hört sich eigentlich nach einem einfachen JOIN an, ich weiß nur nicht, wie ich den in die CASE Bedingung hineinpacken kann.

Zuvor war das kein Problem, weil "tab_b.MengeB" und "tab_b.PreisB" schlicht Standard waren immer eingesetzt werden mußten. Die Spalte A_B ist in der Vorgangstabelle neu hinzugekommen und entsprechend auch die Felder "tab_b.MengeA" und "tab_b.PreisA" in der tab_b - Tabelle.

L.

[dedlfix]

Tach!

Hört sich eigentlich nach einem einfachen JOIN an, ich weiß nur nicht, wie ich den in die CASE Bedingung hineinpacken kann.

Gar nicht. FROM und JOIN sind die ersten Dinge, die in einer Query ausgeführt werden. Die ergeben die Grunddatenmenge, die es zu verarbeiten gilt. Dann kommt WHERE und GROUP BY und nun erst das SELECT in der Auswertungsreihenfolge (abgesehen von Optimierungen der SQL-Engine).

Wenn du in SELECT-Klausel was mit anderen Tabellen machen möchtest, musst du da eine Subquery formulieren, die genau einen Wert als Ergebnis liefert. Die Subquery kann sich natürlich auf die Felder der Haupt-Query beziehen.

dedlfix.

[Lukas.]

Wenn du in SELECT-Klausel was mit anderen Tabellen machen möchtest, musst du da eine Subquery formulieren, die genau einen Wert als Ergebnis liefert. Die Subquery kann sich natürlich auf die Felder der Haupt-Query beziehen.

Hi dedlfix,

Hab mir schon gedacht, dass es per JOIN im SELECT nichts wird. ich wüßte aber auch nicht, wo ich hier subquerymäßig ansetzen sollte.

Daher habe ich die neue Idee, dass ich das über ein weiteres zusätzliches Feld in der tab_b Tabelle lösen könnte. Das würde zwar dann die Spalte A_B der Vorgangstabelle redundant mitführen, aber so schlimm ist das nicht.

L.

[Lukas.]

hI;

Ich habe jetzt mal versucht, die CASE zu verschachteln, aber da meckert mysql.

Warum?

SELECT 
sum(

(CASE 
WHEN (tab_b.A_B = 'A') THEN
CASE
WHEN (tab_b.MengeB = 0 AND tab_b.Art = 'AZ') THEN 0
WHEN (tab_b.MengeB = 0 AND tab_b.Art = 'AK') THEN 0
WHEN tab_b.MengeB = 0 THEN 1
ELSE
tab_b.MengeB
END)
* tab_b.PreisB *

ELSE

CASE
WHEN (tab_b.MengeA = 0 AND tab_b.Art = 'AZ') THEN 0
WHEN (tab_b.MengeA = 0 AND tab_b.Art = 'AK') THEN 0
WHEN tab_b.MengeA = 0 THEN 1
ELSE
tab_b.MengeA
END)
* tab_b.PreisA *

...

Ist da ein Fehler drin oder geht das grundsätzlich nicht in mysql?

L.

[Tabellenkalk]

Hallo,

Ist da ein Fehler drin oder geht das grundsätzlich nicht in mysql?

wenn es grundsätzlich geht, solltest du es aber korrekt verschachteln: Klammern und Schlüsselworte scheinen nicht zu stimmen.

Gruß
Kalk

[Lukas.]

wenn es grundsätzlich geht, solltest du es aber korrekt verschachteln: Klammern und Schlüsselworte scheinen nicht zu stimmen.

Mein Fehler. Aber so gehts auch nicht:

SELECT 
sum(

(CASE 
WHEN (tab_b.A_B = 'A') THEN
(CASE
WHEN (tab_b.MengeB = 0 AND tab_b.Art = 'AZ') THEN 0
WHEN (tab_b.MengeB = 0 AND tab_b.Art = 'AK') THEN 0
WHEN tab_b.MengeB = 0 THEN 1
ELSE
tab_b.MengeB
END)
* tab_b.PreisB *

ELSE

(CASE
WHEN (tab_b.MengeA = 0 AND tab_b.Art = 'AZ') THEN 0
WHEN (tab_b.MengeA = 0 AND tab_b.Art = 'AK') THEN 0
WHEN tab_b.MengeA = 0 THEN 1
ELSE
tab_b.MengeA
END)
* tab_b.PreisA *
END)
...

L.

[Lukas.]

So gehts:

SELECT 
sum(

(CASE 
WHEN (tab_b.A_B = 'A') THEN
(CASE
WHEN (tab_b.MengeB = 0 AND tab_b.Art = 'AZ') THEN 0
WHEN (tab_b.MengeB = 0 AND tab_b.Art = 'AK') THEN 0
WHEN tab_b.MengeB = 0 THEN 1
ELSE
tab_b.MengeB
END)
* tab_b.PreisB

ELSE

(CASE
WHEN (tab_b.MengeA = 0 AND tab_b.Art = 'AZ') THEN 0
WHEN (tab_b.MengeA = 0 AND tab_b.Art = 'AK') THEN 0
WHEN tab_b.MengeA = 0 THEN 1
ELSE
tab_b.MengeA
END)
* tab_b.PreisA
END)
...

Leider kommt bisher nur in 90% der Fälle das korrekte Ergebnis raus... da muß ich aber erstmal schauen, woran das liegt... Query läuft jedenfalls so.

L.

[Tabellenkalk]

Hallo,

WHEN (tab_b.A_B = 'A') THEN ... tab_b.MengeB ... ELSE ... tab_b.MengeA

Wenn A dann B sonst A? Stimmt die zugrundeliegende Logik?

Gruß
Kalk

[Lukas.]

Wenn A dann B sonst A? Stimmt die zugrundeliegende Logik?

Hi Kalk,

natürlich nicht ;) Ist im Original aber auch nicht so.

L.

[Lukas.]

Leider kommt bisher nur in 90% der Fälle das korrekte Ergebnis raus... da muß ich aber erstmal schauen, woran das liegt... Query läuft jedenfalls so.

Alles gut. Die 10% Fehler liegen in der Datei, die mir die Vergleichswerte liefert, dort wird die falsche Spalte gewählt, die in ca. 10% der Fälle andere Werte enthält als es die richtige Spalte hat.

Also alles im grünen Bereich, dank für die Hilfe.

L.

[dedlfix]

Tach!

Hab mir schon gedacht, dass es per JOIN im SELECT nichts wird. ich wüßte aber auch nicht, wo ich hier subquerymäßig ansetzen sollte.

Eine Subquery in der SELECT-Klausel kann dann verwendet werden, wenn man zum einen die Menge der gewünschten Daten in der Hauptquery bereits auf die Zielmenge eingedampft hat (mit FROM, JOIN und WHERE - abzüglich weiterer Ausschlüsse mit HAVING und LIMIT) und zum anderen den Datensatz einer anderen Tabelle mit den Daten des aktuellen Datensatzes ermitteln kann.

Beispiel sei, dass in der Hauptquery Preise stehen, die aber in unterschiedlichen Währungen sein können. Deswegen gibt es ein weiteres Feld mit einem Verweis auf die Währung, die in einer zweiten Tabelle gespeichert wird, weil noch mehr Daten darüber erhoben werden als lediglich das Kürzel EUR oder so. Um nun den Namen der Währung in die Ergebnismenge zu bekommen, kann man joinen. Oder man macht das als Subquery, damit man sich nicht durch den Join die Zwischenmenge unnötig komplex macht.

SELECT preis, (SELECT name FROM waehrungen WHERE id=p.waehrung_id) FROM preise p

Wenn deine Datenmenge also schon fast ok ist, und du über die Kriterien eines Datensatzes genau den Zeildatensatz der anderen Tabelle bestimmen kannst, dann kannst du das so machen.

Eine Eigengschaft von Subquerys gegenüber Joins ist, dass man die Querys und die dabei entstehenden Datenmengen einzeln testen kann und nicht nur irgendein großes Datengemenge aus allen beteiligten Tabellen entsteht.

dedlfix.

[Rolf b]

Vielleicht wird es ja mit einem Tabellenausdruck im FROM und einem UNION ALL übersichtlicher?

-- Variante A
SELECT SUM((CASE WHEN tab.Menge = 0 THEN 1 ELSE tab.Menge) * Preis)
FROM ( 
       SELECT a.MengeA AS Menge, a.PreisA AS Preis
       FROM tab_b A
       WHERE a.A_B = 'A' AND (a.MengeA > 0 OR a.Art NOT IN ('AZ', 'AK'))
     UNION ALL
       SELECT b.MengeB AS Menge, b.PreisB AS Preis
       FROM tab_b B
       WHERE b.A_B = 'B' AND (b.MengeB > 0 OR b.Art NOT IN ('AZ', 'AK'))
     ) tab

-- Variante B
SELECT SUM((CASE WHEN tab.Menge = 0 THEN 1 ELSE tab.Menge) * Preis)
FROM ( 
       SELECT a.MengeA AS Menge, a.PreisA AS Preis FROM tab_b A WHERE a.A_B = 'A'
     UNION ALL
       SELECT b.MengeB AS Menge, b.PreisB AS Preis FROM tab_b B WHERE b.A_B = 'B'
     ) tab
WHERE tab.Menge > 0 OR tab.Art NOT IN ('AZ', 'AK')

Ich wusste jetzt nicht, ob tab_b ein Tabellenname oder ein Alias ist; ich bin mal von einem Tabellennamen ausgegangen. Ob die Aliase A und B nötig sind, müsste man ausprobieren; sie können nicht schaden. Der Alias tab ist auf jeden Fall nötig.

Im FROM werden zwei SELECT-Mengen kombiniert. Für A_B='A' wird MengeA und PreisA ausgewählt, für A_B='B' MengeB und PreisB. Der äußere SELECT kann dann die gefundenen Mengen und Preise aufsummieren, ohne auf A_B achten zu müssen.

Die WHERE Klausel lässt nur die Sätze durch, die entweder eine Menge > 0 haben oder dann, wenn die Menge 0 ist, nicht die Art AZ oder AK haben. Das entspricht deiner "Lösung", bei Menge=0 und Art AZ oder AK den Preis mit 0 zu multiplizieren, sprich: nicht zu summieren.

Ob man diesen Filter an den inneren SELECTs (Variante A) oder in einem eigenen WHERE im äußeren SELECT (Variante B) notiert, sollte vom Ergebnis her egal sein. Ich vermute, dass Variante A schneller ist, weil früher gefiltert wird. Müsste man EXPLAINen oder nachmessen.

Rolf