MySQL Forums
Forum List  »  German

Re: vorherige Summe in die laufende Zeile übertragen
Posted by: Thomas Wiedmann
Date: April 11, 2012 01:56AM

Hallo Alain,

da gibt es noch die Variante mit den MySQL User Variablen (siehe @rownum1 und @rownum2). Damit wird eine künstliche laufende Nr erzeugt, die dann beim JOIN hilft.

SELECT heute.datum, heute.anz1, heute.anz2, heute.anz_ges, gestern.anz_ges_vt
  FROM ( SELECT datum, 
                SUM( CASE knz 
                      WHEN 0 THEN pNr
                      ELSE 0
                     END) AS anz1,
                SUM( CASE knz 
                      WHEN 1 THEN pNr
                      ELSE 0
                     END) AS anz2,
                SUM( CASE knz 
                      WHEN 0 THEN pNr
                      WHEN 1 THEN pNr
                      ELSE 0
                     END) AS anz_ges,
                @rownum1 AS rownum,
                @rownum1 := @rownum1 +1
           FROM ( SELECT @rownum1 := 0 ) init,
                rechnen
           GROUP BY datum
           ORDER BY datum
        ) heute 
  LEFT JOIN ( SELECT datum, 
                SUM( CASE knz 
                      WHEN 0 THEN pNr
                      WHEN 1 THEN pNr
                      ELSE 0
                     END) AS anz_ges_vt,
                @rownum2 AS rownum,
                @rownum2 := @rownum2 +1
           FROM ( SELECT @rownum2 := 0 ) init,
                rechnen
           GROUP BY datum
           ORDER BY datum           
        ) gestern
     ON heute.rownum = gestern.rownum +1
ORDER BY heute.datum;
+------------+------+------+---------+------------+
| datum      | anz1 | anz2 | anz_ges | anz_ges_vt |
+------------+------+------+---------+------------+
| 2012-04-02 |   12 |   13 |      25 |       NULL |
| 2012-04-03 |    4 |    7 |      11 |         25 |
| 2012-04-04 |   22 |    5 |      27 |         11 |
| 2012-04-05 |   10 |   30 |      40 |         27 |
| 2012-04-10 |    9 |    6 |      15 |         40 |
+------------+------+------+---------+------------+
5 rows in set (0.00 sec)

mysql>

Sieht aus, als ob es stimmen würde...

Grüße
Thomas

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: vorherige Summe in die laufende Zeile übertragen
1533
April 11, 2012 01:56AM


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.