LeetCode 512.游戏玩法分析2

奋斗哼哼

于 2023-05-11 20:39:08 发布

阅读量70

点赞数

文章标签：数据库 leetcode mysql 开发语言

本文链接：https://blog.csdn.net/weixin_51696882/article/details/130630251

版权

数据准备

drop table Activity;

Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int);
Truncate table Activity;
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5');
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-05-02', '6');
insert into Activity (player_id, device_id, event_date, games_played) values ('2', '3', '2017-06-25', '1');
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-02', '0');
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5');

需求

请编写一个 SQL 查询，描述每一个玩家首次登陆的设备名称

输入

在这里插入图片描述

输出

with t1 as (
    select player_id,device_id,
       row_number() over (partition by player_id order by event_date) as rn1
    from Activity
)
select player_id,device_id
from t1
where rn1=1
;