Hallo Mark,
mein nächster Versuch. In den Spalten "value" und "score" bekomme ich teilweise die gleichen und teilweise minimal andere Werte raus. Die Ergebnisse der Spalte "L", "M" , "N" sind logischerweise anderst. In Deinen Beispiel "müssen" die Ergebnisse der Spalten "L", "M" , "N" aufgrund des "falschen" GROUP BY andere Ergebnisse liefern.
SELECT max_l.resultDate,
max_l.l AS L,
max_m.m AS M,
max_n.n AS N,
max_l.lv + max_m.mv + max_n.nv AS value,
max_l.ls + max_m.ms + max_n.ns AS score
FROM ( SELECT
id_company,
resultDate,
MAX(L) AS L,
MAX(L * 1734.90 * 0.03225807) AS LV,
MAX((L * 1734.90 * 0.03225807) * factor / 31) AS LS
FROM results
WHERE resultDate <= '2011-12-15' AND resultDate > ('2011-12-15'- INTERVAL 3 DAY)
GROUP BY id_company,resultDate
) max_l
LEFT JOIN ( SELECT
id_company,
resultDate,
MAX(M) AS M,
MAX(M * 33.50 * 0.03225807) AS MV,
MAX((M * 33.50 * 0.03225807) * factor / 31) AS MS
FROM results
WHERE resultDate <= '2011-12-15' AND resultDate > ('2011-12-15'- INTERVAL 3 DAY)
GROUP BY id_company,resultDate
) max_m
ON max_m.id_company = max_l.id_company
AND max_m.resultDate = max_l.resultDate
LEFT JOIN ( SELECT
id_company,
resultDate,
MAX(N) AS N,
MAX(N * 8370.00 * 0.01000000) AS NV,
MAX((N * 8370.00 * 0.01000000) * factor / 31) AS NS
FROM results
WHERE resultDate <= '2011-12-15' AND resultDate > ('2011-12-15'- INTERVAL 3 DAY)
GROUP BY id_company,resultDate
) max_n
ON max_n.id_company = max_l.id_company
AND max_n.resultDate = max_l.resultDate
ORDER BY value DESC;
+------------+---------+---------+--------+---------------------+--------------------------+
| resultDate | L | M | N | value | score |
+------------+---------+---------+--------+---------------------+--------------------------+
| 2011-12-14 | 63.7400 | 0.0000 | 0.0000 | 3567.17886448482000 | 123.55523016151354838710 |
| 2011-12-15 | 0.6500 | 0.0000 | 5.6300 | 507.60794166795000 | 59.49243302340483870968 |
| 2011-12-14 | 4.5200 | 12.4900 | 1.3400 | 378.61491626541000 | 954.35480523499838709677 |
| 2011-12-13 | 5.1800 | 0.0000 | 0.0000 | 289.89624283074000 | 79.43352026748387096774 |
| 2011-12-15 | 0.2200 | 0.0000 | 0.0000 | 12.31219564146000 | 78.87387501105387096774 |
+------------+---------+---------+--------+---------------------+--------------------------+
5 rows in set (0.00 sec)
mysql>
Zu prüfen, ob das Ergebnis stimmt... überlasse ich Dir. Ich denke, es ist noch ein kleiner Fehler bei den MAX() Berechnungen drin.
Grüße
Thomas