Activity table:
----------- ----------- ------------ --------------
| player_id | device_id | event_date | games_played |
----------- ----------- ------------ --------------
| 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 |
----------- ----------- ------------ --------------
Output:
----------- ------------ ---------------------
| player_id | event_date | games_played_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_played) as games_played_so_far
from activity as a1
inner join activity as a2
on a1.event_date >= a2.event_date
and a1.player_id = a2.player_id
group by a1.player_id, a1.event_date
我無法理解這種自我內部連接是如何作業的。誰能解釋一下。我看了幾個視頻，但到目前為止沒有幫助。
uj5u.com熱心網友回復：
當您選擇沒有分組的資料時，您可以更好地看到它的作用 -
對于 a2 中的每一行，它從 a1 中選擇符合條件 a1.event_date >= 的所有行a2.event_date and a1.player_id = a2.player_id并將它們連接在一起
group by 僅將 a2 中具有相同玩家 ID 和 a1.eventdate 的所有行相加
你得到的是一個累積和。
在 MySQL 8 中，使用視窗函式更容易做到這一點。
select a1.player_id, a1.event_date,a2.player_id, a2.event_date from activity as a1 inner join activity as a2 on a1.event_date >= a2.event_date and a1.player_id = a2.player_id
player_id | 事件日期 | player_id | 活動日期
--------: | :--------- | --------: | :---------
1 | 2017-06-25 | 1 | 2016-03-01
1 | 2016-05-02 | 1 | 2016-03-01
1 | 2016-03-01 | 1 | 2016-03-01
1 | 2017-06-25 | 1 | 2016-05-02
1 | 2016-05-02 | 1 | 2016-05-02
1 | 2017-06-25 | 1 | 2017-06-25
3 | 2018-07-03 | 3 | 2016-03-02
3 | 2016-03-02 | 3 | 2016-03-02
3 | 2018-07-03 | 3 | 2018-07-03
select a1.player_id, a1.event_date, sum(a2.games_played) as games_played_so_far from activity as a1 inner join activity as a2 on a1.event_date >= a2.event_date and a1.player_id = a2.player_id group by a1.player_id, a1.event_date ORDER BY a1.player_id, a1.event_dateplayer_id | 事件日期 | games_played_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
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/322919.html
標籤：mysql