Skip navigation links

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


Advanced Search

Re: GREATEST / MAX von berechneten Spalten
Posted by: Thomas Wiedmann ()
Date: April 16, 2012 05:24AM

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

Options: ReplyQuote


Subject Views Written By Posted
GREATEST / MAX von berechneten Spalten 1434 Mark Knochen 04/14/2012 06:44PM
Re: GREATEST / MAX von berechneten Spalten 617 Mark Knochen 04/15/2012 01:48AM
Re: GREATEST / MAX von berechneten Spalten 655 Thomas Wiedmann 04/15/2012 05:49AM
Re: GREATEST / MAX von berechneten Spalten 624 Mark Knochen 04/16/2012 03:29AM
Re: GREATEST / MAX von berechneten Spalten 639 Thomas Wiedmann 04/16/2012 04:05AM
Re: GREATEST / MAX von berechneten Spalten 688 Mark Knochen 04/16/2012 04:38AM
Re: GREATEST / MAX von berechneten Spalten 734 Thomas Wiedmann 04/16/2012 05:24AM
Re: GREATEST / MAX von berechneten Spalten 573 Mark Knochen 04/16/2012 06:13AM
Re: GREATEST / MAX von berechneten Spalten 660 Thomas Wiedmann 04/16/2012 06:38AM
Re: GREATEST / MAX von berechneten Spalten 721 Mark Knochen 04/16/2012 07:04AM
Re: GREATEST / MAX von berechneten Spalten 600 Thomas Wiedmann 04/16/2012 10:00AM
Re: GREATEST / MAX von berechneten Spalten 664 Mark Knochen 04/16/2012 04:04PM
Re: GREATEST / MAX von berechneten Spalten 624 Thomas Wiedmann 04/16/2012 11:50PM
Re: GREATEST / MAX von berechneten Spalten 698 Mark Knochen 04/17/2012 01:33AM
Re: GREATEST / MAX von berechneten Spalten 773 Thomas Wiedmann 04/17/2012 02:11AM
Re: GREATEST / MAX von berechneten Spalten 726 Mark Knochen 04/17/2012 05:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.