1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| SELECT t.user_id AS user_pronos,
u.avatar AS avatar,
u.username AS membre,
coalesce(t.sum_bonus,0) AS bonus,
coalesce(t.sum_booster,0) AS booster_played,
coalesce(t.sum_pt,0) + coalesce(t.sum_bonus,0) AS points,
coalesce(t.best2,0) + coalesce(t.best3,0) AS best_point,
coalesce(t.best3,0) AS best3
FROM users u
LEFT JOIN
(SELECT user_id,
sum(points) AS sum_pt,
sum(booster) AS sum_booster,
sum(bonus_point) AS sum_bonus,
ROUND(SUM(CASE WHEN points=($vic*events.factor) THEN points ELSE 0 END)/($vic*events.factor),0) AS best2,
ROUND(SUM(CASE WHEN points=($vic*$booster_factor) THEN points ELSE 0 END)/($vic*$booster_factor),0) AS best3
FROM pronos
JOIN matchs ON matchs.id = pronos.match_id
JOIN events ON events.id = matchs.event_id
WHERE matchs.date < now()
GROUP BY user_id ) t ON t.user_id = u.id
WHERE u.access!=0
ORDER BY points DESC,
best_point DESC |
Partager