LeetCode-SQL-534. 游戏玩法分析 III
程序员文章站
2022-06-11 14:28:46
...
SELECT C.player_id,C.event_date,C.games_played_so_far
FROM (
SELECT
A.player_id,
A.event_date,
@sum_cnt:=
if(A.player_id = @pre_id AND A.event_date != @pre_date,
@sum_cnt + A.games_played,
A.games_played
)
AS `games_played_so_far`,
@pre_id:=A.player_id AS `player_ids`,
@pre_date:=A.event_date AS `event_dates`
FROM
activity AS A,(SELECT @pre_id:=NULL,@pre_date:=NULL,@sum_cnt:=0) AS B
order BY A.player_id,A.event_date
) AS C
//解法2
SELECT B.player_id,B.event_date,SUM(A.games_played) AS `games_played_so_far`
FROM Activity AS A JOIN Activity AS B
ON (A.player_id = B.player_id AND A.event_date <= B.event_date)
GROUP BY B.player_id,B.event_date