Das ist ein bisschen komplex, keine Ahnung ob das dein MySQL kann, aber so sollte es gehen:

SELECT L.rate_pic_id AS pic_id, R.pic_user_id AS user_id, AVG( rate_point ) AS rate, R.pic_username AS username, S.user_gender AS gender

FROM phpbb_album_rate L

JOIN phpbb_album R

JOIN phpbb_users S ON L.rate_pic_id = R.pic_id

JOIN ( SELECT user_id, MAX(rate) as rate_max FROM

( SELECT L1.rate_pic_id AS pic_id, R1.pic_user_id AS user_id, AVG( L1.rate_point ) AS rate, R1.pic_username AS username, S1.user_gender AS gender

FROM phpbb_album_rate L1

JOIN phpbb_album R1

JOIN phpbb_users S1 ON L1.rate_pic_id = R1.pic_id

AND R1.pic_user_id = S1.user_id

WHERE S1.user_gender =1

GROUP BY rate_pic_id ) x

GROUP BY user_id ) y ON y.user_id = r.pic_user_id

AND R.pic_user_id = S.user_id

WHERE S.user_gender =1

GROUP BY rate_pic_id

HAVING AVG( rate_point) = y.rate_max

ORDER BY AVG(rate_point) DESC;



Einfachere Möglichkeit wäre, das ganze in eine Zwischentabelle zu werfen und dann darin den besten pro User zu suchen.

Gruß

Saluk

---

diu vive floreque.
Spockus

---

erreicht am 04.03.2008 09:47:23

geschrieben von Saluk , 07.02.2006, 10:15 Uhr , 34 mal gelesen