Mehrere Abfragen in einem Query

[BiOG]

ahoi!
ich hoffe ihr könnt mir helfen, weil ich inzwischen den wald vor bäumen nicht mehr seh...

mein system: debian server mit mysql 5.1 und php 5.2

ich habe folgende tabelle ("buchungen") mit den feldern:

  id    => unique, primary und autoincrement, 
  e_id  => id eines events
  u_id  => id des users
  wert  => ganzzahliger wert
  pro   => ganzzahliger wert (prozente)
  erg   => ganzzahliger wert (ergebnis aus prozente von wert)
  datum => yyyy-mm-dd h:i:s 

dann gibt es noch eine tabelle mit den userdaten ("profil"):

  id    => id des users
  name  => varchar
  email => varchar
  usw... 

und eine tabelle die als klickcounter fungiert und auch so heisst

  id    =>   
  u_id  => id des users
  datum => yyyy-mm-dd
  std   => volle stunde des klicks (also 0-23)
  cnt   => der zähler 

es kann ein datumsbereich gewählt werden und ich möchte für diese zeitspanne eine "top10" erstellen die mir folgende daten ausgibt:

* name und email (aus "profil")
* anzahl der klicks (aus "klickcounter") dieses users im datumsbereichs
* u_id (aus "buchungen")
* vorkommen mit der "e_id" = 1 für diesen user im datumsbereich (aus "buchungen")
* vorkommen mit der "e_id" = 2, 3, 4 für diesen user im datumsbereich (aus "buchungen")
* vorkommen mit der "e_id" >= 1000 <= 2000 für diesen user im datumsbereich (aus "buchungen")
* vorkommen mit der "e_id" >= 2000 <= 3000 für diesen user im datumsbereich (aus "buchungen")
* summe aller "wert" felder für diesen user im datumsbereich (aus "buchungen")
* summe aller "erg" felder für diesen user im datumsbereich (aus "buchungen")

mein bisheriger ansatz sieht so aus:

    SELECT
        count(a.u_id) AS anzahl_e1,
        sum(a.wert) AS wert,
        sum(a.erg) AS ergebnis,
        a.u_id,
        b.name,
        b.email
    FROM
        buchungen a,
        profil b
    WHERE
        a.e_id = 1 AND 
        a.datum >= '".$dp1."' AND 
        a.datum <= '".$dp2."' AND 
        a.u_id = b.u_id
    GROUP BY
        a.u_id
    ORDER BY 
        wert DESC
    LIMIT 10 

damit bekomme ich immerhin die anzahl der vorkommen von e_id=1 und die entsprechende summe von "wert" und "gesamt" (und namen und email natürlich)
mit angepasster where klausel erhalte ich natürlich auch die werte für die anderen events, aber wie bringe ich das alles in einen query?! falls das überhaupt möglich ist...
also, ich hätte gerne meine top10 ausgabe mit einem query erschlagen (idealerweise auch noch die abfrage des klickcounters)

geht das? und wo ist mein denkfehler? ich sitz da jetzt jedenfalls schon 2 tage dran und komm nicht weiter...

[threadi]

Verwende LEFT JOIN um die Klicks einzubinden, denn die könnten ja auch 0 sein bei einem Profil. Siehe Manual:
http://dev.mysql.com/doc/refman/5.1/de/join.html
http://dev.mysql.com/doc/refman/5.1/...imization.html

[Falk Prüfer]

Hallo,

ein einfacher LEFT JOIN reicht nicht! Damit würde das Ergebnis für die Summierung der a.wert und a.erg Felder verfälscht werden. Der LEFT JOIN muss hier über eine bereits gruppierte Subquery (die die aggegierten Werte enthält ) erfolgen.

[highlight=sql]
select ..., c.klick_count
from buchungen a
inner join profil b on b.u_id = a.u_id
left join (
select i.u_id, count(i.id) klick_count
from klickcounter i
where i.datum between '".$dp1."' AND '".$dp2."'
group by i.u_id
) c on c.u_id = a.u_id
where (a.e_id in (1, 2, 3, 4) or a.e_id between 1000 and 3000)
AND a.datum between '".$dp1."' AND '".$dp2."'
[/highlight]

Gruß Falk

[BiOG]

danke für eure antworten.
leider haut das so noch nicht hin.
den klickcounter mit in den query zu nehmen ist auch nicht mein hauptproblem/wunsch, den kann ich dann auch nochmal separat abfragen...

ich habe mir jetzt noch mal testtabellen angelegt mit den namen "test_buchungen", "test_klickcounter", "test_profil" - alles andere wie oben

diese habe ich jetzt mit testdaten gefüllt um das ergebnis genau nachvollziehen zu können:

in der test_profil tabelle gibt es 5 einträge:

INSERT INTO `test_profil` (`id`, `u_id`, `name`) VALUES
(1, 4, 'vierter'),
(2, 1, 'erster'),
(3, 5, 'fünfter'),
(4, 3, 'dritter'),
(5, 2, 'zweiter'); 

in der test_buchungen tabelle sind insgesamt 180 einträge
für den user 2 sind es 30 einträge/buchungen:
10 x event 2 mit dem wert 10 (gesamteventwert 100)
5 x event 1002 mit dem wert 10 (gesamteventwert 50)
5 x event 3 mit dem wert 20 (gesamteventwert 100)
3 x event 2002 mit dem wert 10 (gesamteventwert 30)
7 x event 4 mit dem wert 10 (gesamteventwert 70)

für den user 4 sind es 60 einträge/buchungen - das selbe wie bei user 2, nur doppelt und bei user 1 schliesslich 90 einträge (wie user 2, nur 3 mal)

die test_klickcounter hat 24 einträge:
der user 2 hat am 1.4. 40 und am 15.4. 20 klicks bekommen (gesamt april 60)
der user 3 hat am 2.4. 80 und am 16.4. 40 klicks bekommen (gesamt april 120)
der user 4 hat am 3.4. 8 und am 15.4. 120 klicks bekommen (gesamt april 128)

nun möchte ich eine "top10" tabelle die mir für einen bestimmten zeitraum sortiert nach bestimmten kriterien folgendes liefert:

zeitraum 01.04. - 30.04.
sortiert nach event_2,3,4


uid | name | klicks | event_1 | event 2,3,4 | event 1000-1999 | event 2000-2999 | wert
1 | erster | 0 | 0 | 66 | 15 | 9 | 405-75+45=375
4 | vierter | 128 | 0 | 44 | 10 | 6 | 270-50+30=250
2 | zweiter | 60 | 0 | 22 | 5 | 3 | 135-25+15=125
3 | dritter | 120 | 0 | 0 | 0 | 0 | 0-0+0=0
5 | fünfter | 0 | 0 | 0 | 0 | 0 | 0-0+0=0


"wert" setzt sich dabei zusammen aus:
(e_2 + e_3 + e_4) - (summe(e_1000 bis e_1999) + (summe(e_2000 bis e_2999)


falks query oben hat mich leider auch nicht weiter gebracht...
ich habe mal das setup von meinem db-test als test_sql.txt angehangen, vielleicht hat ja jemand zeit und lust sich dessen anzunehmen...

nochmals danke und bis dann

p.s.: wie ist denn der BB-Code für sql quelltext?

[Falk Prüfer]

..."wert" setzt sich dabei zusammen aus:
(e_2 + e_3 + e_4) - (summe(e_1000 bis e_1999) + (summe(e_2000 bis e_2999)

Anhand deiner Beisieldaten kann ich deine gewünschten "Werte" zwar nicht nachvollziehen (Bei mir kommt immer das Doppelte raus), aber die Query könnte in etwa so aussehen:
[highlight=sql]
select p.u_id, p.name, ifnull(c.klicks, 0) klicks,
sum(if(b.e_id = 1, 1, 0)) event_1,
sum(if(b.e_id in (2,3,4), 1, 0)) event_2_3_4,
sum(if(b.e_id >= 1000 and b.e_id < 2000, 1, 0)) event_1000_1999,
sum(if(b.e_id >= 2000 and b.e_id < 3000, 1, 0)) event_2000_2999,
sum(if(b.e_id in (2,3,4), b.wert, 0))
- sum(if(b.e_id >= 1000 and b.e_id < 2000, b.wert, 0))
+ sum(if(b.e_id >= 2000 and b.e_id < 3000, b.wert, 0)) wert
from test_profil p
left join test_buchungen b on b.u_id = p.u_id
and b.datum between '2011-04-01' and '2011-04-30'
left join (
SELECT i.u_id, sum(i.counter) klicks
FROM test_klickcounter i
WHERE i.datum BETWEEN '2011-04-01' and '2011-04-30'
GROUP BY i.u_id
) c on c.u_id = p.u_id
group by p.u_id
order by wert desc
limit 10
[/highlight]

...p.s.: wie ist denn der BB-Code für sql quelltext?

Das steht hier: Formatierung von SQL in Beiträgen

Gruß Falk

[BiOG]

Yes!
super, tausend dank!
hätte ich nie hingekriegt...
das haut genau hin und die werte stimmen in der realen DB auch.