Hallo Mark,
danke für das ausführliche Beispiel. Aber sonst wäre es auch nicht zu verstehen:
a) Idee - Prozess auftrennen mit Temporare Tables
Die Abfrage leicht angepaßt, damit keine doppelten Spaltennamen entstehen
SELECT items.*, invoicelines.qty,
IF(( ( SUM((SELECT SUM(items_stock.physicalstock)
FROM items_stock
WHERE items_stock.itemid = items.itemid)) ) / ( SUM(IF(
invoicelines.qty > 0,
invoicelines.qty, 0)) / ( 1 ) ) ) IS NULL, 0, ( (
SUM((SELECT SUM(items_stock.physicalstock)
FROM items_stock
WHERE items_stock.itemid = items.itemid)) ) / ( SUM(
IF(
invoicelines.qty > 0, invoicelines.qty,
0)) / ( 1 ) ) )) AS rangedays
FROM items
LEFT JOIN(SELECT DISTINCT itemid,
SUM(qty) AS qty
FROM invoicelines
GROUP BY itemid
ORDER BY NULL) invoicelines
ON( invoicelines.itemid = items.itemid )
GROUP BY items.itemid;
+----------+--------+-----------+-------+------+-----------+
| ItemName | itemId | id_gender | brand | qty | rangedays |
+----------+--------+-----------+-------+------+-----------+
| Suit1 | 1 | 1 | Suit | NULL | 0 |
| Suit2 | 2 | 2 | Suit | 5 | 5.0000 |
| Suit3 | 3 | 1 | Suit | 3 | 5.0000 |
| Test1 | 4 | 1 | Test | 4 | 4.7500 |
| Test2 | 5 | 2 | Test | 3 | 7.6667 |
| Test3 | 6 | 1 | Test | 2 | 7.5000 |
+----------+--------+-----------+-------+------+-----------+
6 rows in set (0.00 sec)
mysql>
a1) Temporäre Tabelle anlegen
CREATE TEMPORARY TABLE temp_range_result AS
SELECT items.*, invoicelines.qty,
IF(( ( SUM((SELECT SUM(items_stock.physicalstock)
FROM items_stock
WHERE items_stock.itemid = items.itemid)) ) / ( SUM(IF(
invoicelines.qty > 0,
invoicelines.qty, 0)) / ( 1 ) ) ) IS NULL, 0, ( (
SUM((SELECT SUM(items_stock.physicalstock)
FROM items_stock
WHERE items_stock.itemid = items.itemid)) ) / ( SUM(
IF(
invoicelines.qty > 0, invoicelines.qty,
0)) / ( 1 ) ) )) AS rangedays
FROM items
LEFT JOIN(SELECT DISTINCT itemid,
SUM(qty) AS qty
FROM invoicelines
GROUP BY itemid
ORDER BY NULL) invoicelines
ON( invoicelines.itemid = items.itemid )
GROUP BY items.itemid;
Query OK, 6 rows affected, 1 warning (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 1
mysql>
a2) Jetzt kann das "Zwischenergebnis" einfach weiter ausgewertet werden.
SELECT * FROM temp_range_result
WHERE rangedays BETWEEN 4 AND 5;
+----------+--------+-----------+-------+------+-----------+
| ItemName | itemId | id_gender | brand | qty | rangedays |
+----------+--------+-----------+-------+------+-----------+
| Suit2 | 2 | 2 | Suit | 5 | 5.0000 |
| Suit3 | 3 | 1 | Suit | 3 | 5.0000 |
| Test1 | 4 | 1 | Test | 4 | 4.7500 |
+----------+--------+-----------+-------+------+-----------+
3 rows in set (0.00 sec)
mysql>
Temporäre Tabellen werden beim Ende der MySQL-Session automatisch gelöscht.
Wäre mal eine Notlösung. Alles andere dauert meinerseits länger, da der komplette SQL umgebaut werden muß.
Grüße
Thomas