MySQL Forums :: German :: GREATEST / MAX von berechneten Spalten

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