How to get multiple max column value mysql
I am trying to query tables using join but I can't get success here my tables
Score
id , user_id , score , week
1 , 123 , 2 , 1
2 , 432 , 2 , 1
3 , 432 , 3 , 1
4 , 123 , 2 , 2
I want to query top twenty users records on the bases of sum of week 1 and week 2's max total
I want the result should be
id , user_id , week1score , week2score
1 , 123 , 2 开发者_Python百科 , 2
2 , 432 , 3 , 0
The formula is order by descending sum(max(week1)+max(week2))
Thanks Inam
something like this would do
SELECT t1.user_id,
SUM(week1score) AS week1score,
SUM(week2score) AS week2score
FROM (SELECT user_id
FROM `table`
GROUP BY user_id) AS t1
JOIN (SELECT user_id,
Max(IF(`week` = 1, score, 0)) AS week1score,
Max(IF(`week` = 2, score, 0)) AS week2score
FROM `table`
GROUP BY user_id,
`week`) t2
ON t2.user_id = t1.user_id
GROUP BY user_id
ORDER BY ( SUM(week1score) + SUM(week2score) ) DESC
You need a composit index on the following columns
- (user_id,week,score)
精彩评论