MySQL select Abfrage mit spezieller Gruppierung
Hallo Leute,
ich stehe zur Zeit bei einer etwas (für mich) komplexen mySQL bzw. PHP Abfrage. Vielleicht könnt ihr mir einen Schubs in die richtige Richtung geben?!
Ich frage eine Tabelle mit verschiedenen Spalten ab, die auch verschiedene Werte beinhalten, die ich via AVG bzw. SUM berechne. Soweit so gut. In einer Spalte allerdings sind verschiedene Produktnamen die ich gruppieren will, allerdings gruppieren solange ein bestimmtes Zeichen im Produktnamen nicht erreicht ist.
Beispielabfrage:
SELECT COUNT( `product` ) AS anzahl, AVG( `aufwand` ) AS aufwand, `product` FROM `tabelle` WHERE 1 AND datum BETWEEN "$datumsvariable" AND "$datumsvariable2'" AND `product` != "" AND `product` != "\"\"" GROUP BY `product` ORDER BY `aufwand`
Soweit so gut... Allerdings gibt es nun eben diese Produktnamen mit Klammern "(", die Details beinhalten die ich nicht als eigene Gruppe will. zB.
- produktname AB
- produktname CD
- produktname CD (details bla bla)
- produktname CD (details bla bla)
- produktname AB
- produktname AB (details bla bla)
- produktname CD
Meine obige mySQL-Abfrage würde nun 4 Gruppen machen:
- produktname AB
- produktname AB (details bla bla)
- produktname CD
- produktname CD (details bla bla)
Ich will aber
- produktname AB
- produktname CD
Die produktname AB (details bla bla) und produktname CD (details bla bla) sollen enthalten sein in produktname AB bzw. produktname CD
Ich hoffe das war soweit verständlich und ihr könnt mir helfen.
Danke im Voraus, lg DeepX
5 Antworten
Würde man für gruppierte Spalten nicht "HAVING" verwenden?
Jedenfalls würde ich nicht auf != (ungleich) abfragen, sonden NOT LIKE verwenden. Dann kannst du Platzhalter verwenden. Z. B. not like "%(%)"
Dann musst du mal mit SUBSTRING die Zeichenkette bis zur Klammer kürzen. Die Klammer findest du mit INSTR
https://dev.mysql.com/doc/refman/5.0/en/string-functions.html
Wenn du fitt in regulären Ausdrücken bist, kannst du vielleicht auch REGEXP oder RLIKE verwenden.
PS: REGEXP liefert nur 1 (Match) oder 0 (kein Match), nicht aber den Teilstring. Somit kommt das zur Lösung deines Problems nicht infrage.
Hello there
Ist das wirklich so kompliziert? ich bin irritiert.
Ganz abgesehen davon, dass du vermutlich ungeschickt deine Tabelle designt hast.
Wenn deine Gruppierung nach nem bestimmten Teil erfolgen soll, der immer gleich ist, zum Beispiel immer Produktname AB oder Produktname CD
könntest du folgendermaßen vorgehen...
SELECT .....
FROM...
WHERE....
GROUP BY substring(product, -length(product), 3);
Bei dieser Formulierung würde so gruppiert werden, dass nicht nach vollständigen Produktnamen gruppiert werden würde, sondern nur nach den ersten drei Zeichen. Das klappt halt natürlich nur, wenn der Anfangsteil immer gleich bzw gleich lang ist. Wenn das nicht der Fall ist, wirds komplizierter, dann ist evtl entweder ein RegExp-Vergleich nötig oder du musst nach einem DELIMITER Ausschau halten, zum Beispiel ein Leerzeichen, das würde auch mit substring() gehen.
https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index
Hoffe ich konnte dir helfen
MfG
Alex
Hello,
ja, manchmal sind scheinbar einfache Abfragen, recht komplex. Ist mir nicht neu *g* - Aber danke auch für deine Lösung. Leider hab ich die Tabelle nicht erstellt... das wäre ja noch schöner *haha*, sondern muss mit dem arbeiten, das vorhanden ist.
Aber danke trotzdem nochmal :)
auch wenn es doch eine lösung für dein problem gab - es ist etwas krampfig. dein problem weist auf ein ganz anderes hin: das db-design ist schlecht: die klammerausdrücke gehören nicht zum produktnamen, sondern in eine eigne spalte (ohne klammern). die ausdrücke "produktname (details)" baust du erst für die ausgabe zusammen - entweder in der sql-select-liste oder per php.
damit kannst du dann ganz einfach deine gruppierung formulieren - ohne purzelbäume.
Hallo, danke für deine Zeit und deinen Kommentar. Wirklich.
Nur leider war/bin ich nicht für das mySQL Design dieser Tabelle zuständig. Deshalb auch meine Anfrage für eine Lösung. Insofern kann ich deine Antwort leider auch nicht werten, da sie mir (und anderen) nicht wirklich weiter hilft. Trotzdem: Danke :D
auch dann würde ich versuchen, das design ändern zu lassen - schon zu deiner sicherheit. nicht dass dir dann der vorwurf gemacht wird: "häste doch was gesagt". (es sei denn, das design kommt vom lieben gott, dann wirds 300 jahre dauern bis sich was ändert)
ansonsten: du überführst die tabelle via php in eine temporary table mit bereinigtem design. das aufsplitten geht in php in eine zeile:
$newproduct = trim(array_shift(explode('(',$product)));
300 Jahre.... wenns so schnell ginge *ggg* - Aber danke, hab ja schon eine Lösung erarbeitet, danke dir bzw. euch :)
Suboptimierer hat mich auf den richtigen Pfad gebracht :)
Anbei meine Abfrage ohne Datumseinschränkung (da das ja das Wenigste ist)
SELECT COUNT( `product` ) AS anzahl, AVG( `aufwand` ) AS aufwand, `product`, LEFT(`product`, INSTR(`product`, " (")) AS productclear FROM `tabelle` WHERE 1 AND `product` != "" AND `product` != "\"\"" GROUP BY `productclear` ORDER BY `aufwand` DESC
Hmm... doch nicht so ganz :(
Denn Mittels LEFT und INSTR ermittle ich ja das Leerzeichen mit Klammer " (". Und gebe dann den String bis zu dieser Position aus.
Allerdings werden nun die Produkte ohne " (" gänzlich ignoriert, da die Position dieser Abfrage ja = Null ist.
Für jeden der es auch mal braucht, UNION ALL hat geholfen:
Zuerst alle Produkte mit Klammern bereinigen
(SELECT product, COUNT( `product` ) AS anzahl, AVG( `aufwand` ) AS aufwand, `product` , LEFT( `product` , INSTR( `product` , " (" ) ) AS productclear FROM `tabell` WHERE 1 AND datumBETWEEN "$datumsvariable" AND "$datumsvariable2" AND `product` != "" AND `product` != "\"\"" GROUP BY productclear)
dann jene Produkte ohne Klammern abfragen
( SELECT product, COUNT( `product` ) AS anzahl, AVG( `aufwand` ) AS aufwand, `product` , LEFT( `product` , 200 ) AS productclear FROM `tabelle` WHERE 1 AND INSTR( `product` , " (" ) =0 AND DATE BETWEEN "$datumsvariable" AND "$datumsvariable2" AND `product` != "" AND `product` != "\"\"" GROUP BY productclear )
Fertig.
Wie erwähnt mit UNION ALL zusammenführen :)
Ich geb mal einen DH, dafür dass du selbst innerhalb der Frage dir selbst mit kleinen Anreizen die Antwort erarbeitet hast.
Es gäbe noch eine Alternative Lösung zur "Union All", und zwar unter der Verwendung von Case-When:
SELECT COUNT(`product` ) AS anzahl, AVG( `aufwand` ) AS aufwand, LEFT(`product`, CASE INSTR(`product`, " (") WHEN 0 THEN LEN(`product`) ELSE INSTR(`product`, " (") ) AS productclear FROM `tabelle` WHERE `product` != "" AND `product` != "\"\"" GROUP BY `productclear` ORDER BY `aufwand` DESC
D.h. Wenn der 'INSTR' Befehl bei nicht gefundener Klammer eine 0 Zurück liefern würde, wird stattdessen die die Gesamtlänge des Feldes an die Funktion: 'LEFT' übermittelt.
Ohhhh.... das ist ja noch besser und kannt ich noch gar nicht. Besten Dank!! Und das ich selbst Antworte erarbeite liegt in meiner Natur. Steht ja auch in meiner Frage, dass ich um Möglichkeiten bat, nicht um eine direkte Lösung :)
ich nehme jetzt einfach mal an, dass bei dir in der Spalte: "Product" sowohl werte drin stehen, die wirklich so heißen: Also mal mit klammer Details und mal ohne.
Demnach musst du die Werte in der Klammer herausschneiden, und zwar überall wo du die Spalte "Product" verwendest.
Methoden die dir hier helfen, das 'herauszuschneiden' wären (ich hoffe die gibt es unter MySQL auch): CHARINDEX, LEFT, LEN, TRIM
Danke, "having" kannte ich bis dato noch nicht. Muss ich mir mal ansehen.
Aber so wie du es schreibst, schließt du jene Produktnamen mit Klammern (also zB produktname CD (details bla bla)) in deiner Abfrage aus, oder?!
Das will ich aber nicht... sie sollen berücksichtigt werden, allerdings bei der Gruppierung von "produktname CD"