欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

LeetCode-SQL-534. 游戏玩法分析 III

程序员文章站 2022-06-11 14:28:46
...

LeetCode-SQL-534. 游戏玩法分析 III

LeetCode-SQL-534. 游戏玩法分析 III

LeetCode-SQL-534. 游戏玩法分析 III

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

 

相关标签: LeetCode 数据库