获取每个唯一 ID 的最后 5 行的总和 [英] Get sum of last 5 rows for each unique id

问题描述

这是我目前使用的查询

SELECT SUM(score) as score FROM (SELECT p2.score FROM performance_buzz as p2 WHERE p2.player_id = 922 ORDER BY p2.id DESC LIMIT 5) as performance_buzz

但是在上面的查询中，我需要手动传递 player_id 而我不想这样做.我想用 mysql 的方式来做这个，因为我想使用这个查询作为子查询来获取每个玩家的最后 5 行得分总和

But in the above query I need to pass player_id manually and I don't want to do that. I want to do this with the mysql way because I want to use this query as subquery for getting last 5 rows sum of score for each player

SELECT performance_buzz.id, performance_buzz.score as last_score, performance_buzz.name

FROM `performance_buzz` 

LEFT JOIN performance_buzz m2 ON (performance_buzz.name = m2.name AND performance_buzz.id < m2.id)  

WHERE m2.id IS NULL 

GROUP BY performance_buzz.name 

ORDER BY performance_buzz.id DESC

推荐答案

应该是这样的:

select player_id, sum(score) score
from
(
    SELECT 
        @row_number:=CASE
            WHEN @player_id = player_id THEN @row_number + 1
            ELSE 1
        END AS rn,
        @player_id:=player_id as player_id,
        score
    FROM
        performance_buzz,(SELECT @player_id:=0,@row_number:=0) as t
   order by player_id, id desc
) a
where rn <= 5
group by player_id

这篇关于获取每个唯一 ID 的最后 5 行的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！