Hallo Thomas,
eigentlich geht es wieder um die "Riesenabfrage" von vor einiger Zeit, das Beispiel hier kürze ich mal herunter. Zuerst die Tabellen mit Beispieldaten:
--
-- Tabellenstruktur für Tabelle `invoicelines`
--
CREATE TABLE IF NOT EXISTS `invoicelines` (
`itemId` int(11) NOT NULL,
`qty` int(3) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Daten für Tabelle `invoicelines`
--
INSERT INTO `invoicelines` (`itemId`, `qty`) VALUES
(2, 2),
(2, 3),
(3, 1),
(3, 2),
(4, 2),
(4, 2),
(5, 1),
(5, 2),
(6, 1),
(6, 1);
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `items`
--
CREATE TABLE IF NOT EXISTS `items` (
`ItemName` varchar(10) NOT NULL,
`itemId` int(11) NOT NULL,
`id_gender` int(11) NOT NULL,
`brand` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Daten für Tabelle `items`
--
INSERT INTO `items` (`ItemName`, `itemId`, `id_gender`, `brand`) VALUES
('Suit1', 1, 1, 'Suit'),
('Suit2', 2, 2, 'Suit'),
('Suit3', 3, 1, 'Suit'),
('Test1', 4, 1, 'Test'),
('Test2', 5, 2, 'Test'),
('Test3', 6, 1, 'Test');
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `items_stock`
--
CREATE TABLE IF NOT EXISTS `items_stock` (
`itemStockId` int(11) NOT NULL,
`itemId` int(11) NOT NULL,
`brand` varchar(20) NOT NULL,
`PhysicalStock` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Daten für Tabelle `items_stock`
--
INSERT INTO `items_stock` (`itemStockId`, `itemId`, `brand`, `PhysicalStock`) VALUES
(1, 1, 'Suit', 10),
(2, 1, 'Suit', 5),
(3, 2, 'Suit', 20),
(4, 2, 'Suit', 5),
(5, 3, 'Suit', 10),
(6, 3, 'Suit', 5),
(7, 6, 'Test', 7),
(8, 6, 'Test', 8),
(9, 4, 'Test', 9),
(10, 4, 'Test', 10),
(11, 5, 'Test', 11),
(12, 5, 'Test', 12);
Mit folgendem Select soll die Reichweite - also, wie lange ein Produkt braucht, um verkauft zu werden, bezogen auf eine bestimmte Zeitspanne - mit ausgegeben werden (ich habe in dem Fall nur 1 Tag als Zeitspanne genommen, live würde das dynamisch anhand eines Zeitraumes berechnet werden)
SELECT * ,
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 | itemId | qty | rangeDays |
+----------+--------+-----------+-------+--------+------+-----------+
| Suit1 | 1 | 1 | Suit | NULL | NULL | 0 |
| Suit2 | 2 | 2 | Suit | 2 | 5 | 5.0000 |
| Suit3 | 3 | 1 | Suit | 3 | 3 | 5.0000 |
| Test1 | 4 | 1 | Test | 4 | 4 | 4.7500 |
| Test2 | 5 | 2 | Test | 5 | 3 | 7.6667 |
| Test3 | 6 | 1 | Test | 6 | 2 | 7.5000 |
+----------+--------+-----------+-------+--------+------+-----------+
6 rows in set (0.00 sec)
Nun soll per Filter auch möglich sein, nur die "items" zu selektieren, die z.B. einen Wert von rangeDays BETWEEN '4' AND '5' haben
SELECT * ,
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
HAVING rangeDays BETWEEN '4' AND '5'
+----------+--------+-----------+-------+--------+------+-----------+
| ItemName | itemId | id_gender | brand | itemId | qty | rangeDays |
+----------+--------+-----------+-------+--------+------+-----------+
| Suit2 | 2 | 2 | Suit | 2 | 5 | 5.0000 |
| Suit3 | 3 | 1 | Suit | 3 | 3 | 5.0000 |
| Test1 | 4 | 1 | Test | 4 | 4 | 4.7500 |
+----------+--------+-----------+-------+--------+------+-----------+
3 rows in set (0.01 sec)
Der SELECT funktioniert prima, wenn man auf die einzelnen "items" gruppiert.
Wenn ich aber nach items.brand gruppiere, dann bekomme ich kein Ergebnis zurück, da sich die HAVING Klausel auf die berechneten und schon gruppierten Werte von rangeDays bezieht.
SELECT *,
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.brand HAVING rangeDays BETWEEN '4' AND '5'
sollte also eigentlich beide "brands" listen, mit den Mittelwerten aus den enthaltenen "items", die rangeDays BETWEEN '4' AND '5' haben.
Puh, etwas länger geworden ... wie gesagt, das ganze ist eigentlich wieder Teil der doch sehr komplexen Abrage aus
http://forums.mysql.com/read.php?70,510668,514338#msg-514338
und sollte nicht weiter an Zeit fressen :)
Danke schonmal
Mark