Mysql刷题笔记 12.29 查询每个玩家每天累计玩的游戏数量有多少？

Mysql练习

-- 12.29 查询每个玩家每天累计玩的游戏数量有多少？
CREATE TABLE T1229(
	player_id INT,
  device_id INT,
  event_date DATE,
  games_player INT
)
INSERT INTO T1229 VALUES(1,2,"2016/03/01",5)
INSERT INTO T1229 VALUES(1,2,"2016/05/02",6);
INSERT INTO T1229 VALUES(1,3,"2017/06/25",1);
INSERT INTO T1229 VALUES(3,1,"2016/03/02",0);
INSERT INTO T1229 VALUES(3,4,"2018/07/03",5);

T1229
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_player |
+-----------+-----------+------------+--------------+
|         1 |         2 | 2016-03-01 |            5 |
|         1 |         2 | 2016-05-02 |            6 |
|         1 |         3 | 2017-06-25 |            1 |
|         3 |         1 | 2016-03-02 |            0 |
|         3 |         4 | 2018-07-03 |            5 |
+-----------+-----------+------------+--------------+

结果
+-----------+------------+---------------------+
| player_id | event_date | games_player_so_far |
+-----------+------------+---------------------+
|         1 | 2016-03-01 |                   5 |
|         1 | 2016-05-02 |                  11 |
|         1 | 2017-06-25 |                  12 |
|         3 | 2016-03-02 |                   0 |
|         3 | 2018-07-03 |                   5 |
+-----------+------------+---------------------+

SELECT a1.player_id,a1.event_date,SUM(a2.games_player) games_player_so_far
FROM T1229 a1,T1229 a2
WHERE a1.player_id = a2.player_id AND a1.event_date >= a2.event_date
GROUP BY a1.player_id,a1.event_date
ORDER BY player_id,event_date