MySQL Forums

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

Subject
Views
Written By
Posted
2204
April 14, 2012 06:44PM
893
April 15, 2012 01:48AM
1080
April 15, 2012 05:49AM
1090
April 16, 2012 03:29AM
972
April 16, 2012 04:05AM
1327
April 16, 2012 04:38AM
Re: GREATEST / MAX von berechneten Spalten
1048
April 16, 2012 05:24AM
873
April 16, 2012 06:13AM
993
April 16, 2012 06:38AM
1061
April 16, 2012 07:04AM
953
April 16, 2012 10:00AM
975
April 16, 2012 04:04PM
1080
April 16, 2012 11:50PM
974
April 17, 2012 01:33AM
1747
April 17, 2012 02:11AM
981
April 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.