题目：

有如下一张表 Activity: image

获取player首次登录设备的device_id，返回的结果如下：

image

参考答案：

数据库版本：Server version: 8.0.20 MySQL Community Server - GPL

建表语句

create table dailytest_20200527
(
    player_id    int,
    device_id    int,
    event_date   date,
    games_played int
);

数据准备

insert into dailytest_20200527 values(1,2,'2016-03-01',5);
insert into dailytest_20200527 values(1,2,'2016-05-02',6);
insert into dailytest_20200527 values(2,3,'2017-06-25',1);
insert into dailytest_20200527 values(3,1,'2016-03-02',0);
insert into dailytest_20200527 values(3,4,'2018-07-03',5);

查询逻辑

SELECT 
       AA.player_id, 
       AA.device_id
FROM (select 
             player_id, 
             device_id, 
             row_number() over (partition by player_id order by event_date asc ) as rn
      from dailytest_20200527) AA
where AA.rn = 1;

附：
题目来源：https://mp.weixin.qq.com/s/TO7jDR649Xcjl4f8OOgeSQ