Sum of x highest per row in mysql
I have a MYSQL table with the columns (userid, score) . I want to group the userid 's and get a column with the sum of the X highest scores for each userid .
Eg For the sum of the two highest scores:
userid | score
-------+-------
01 | 1
01 | 1
01 | 2
02 | 1
02 | 2
02 | 3
to:
userid | scoresum
-------+----------
01 | 3
02 | 5
But I can't seem to figure out how to do this in MYSQL.
select
userid,
(
select sum(highestscores)
from (
select *
from userscore us2
where us2.userid = us1.userid
order by score desc limit 5
)
) as scoresum
from ( select distinct userid from userscore ) us1
so basically you need a sub query to get the 5 highest scores. you then sum those with another sub query. and you run that whole business for each unique user_id from your one and only table, userscore.
select SUM(MAX(col.name)) From table name Group by usedid
this is a simple query that gives you all the data. now you just have to split the top_scores by /,/ and then add them up in your code. an alternative is a stored procedure but i think this is simpler. good luck.
select GROUP_CONCAT(score ORDER BY score DESC) top_scores
from userscore
group by userid
上一篇: MySQL数据透视表
下一篇: mysql中每行最高x的总和