牛客每个人最近的登录日期(六)题解
牛客每个人最近的登录日期(六)
http://www.nowcoder.com/questionTerminal/572a027e52804c058e1f8b0c5e8a65b4
select user_id,date,sum(number) over(partition by user_id order by date) from passing_number
与
select pn1.user_id,pn1.date,sum(pn2.number) from passing_number pn1,passing_number pn2 where pn1.user_id=pn2.user_id and pn1.date>=pn2.date group by pn1.user_id,pn1.date;这2种方法，均可以得到某一天，id为多少的用户，在这一天和这一天之前，通过的题目总数，id和日期，然后和登录(login)表，用户(user)表连接，再按照题目要求的东西排序，就可以得到最后的结果了:
select user.name as u_n, login.date, p1.ps_num from login join (select user_id, date ,sum(number) over(partition by user_id order by date) ps_num from passing_number) p1 on p1.user_id=login.user_id and p1.date=login.date join user on login.user_id=user.id order by login.date, user.name与以下语句等价:
select user.name as u_n, login.date, p1.ps_num from login join (select pn1.user_id,pn1.date,sum(pn2.number) as ps_num from passing_number pn1,passing_number pn2 where pn1.user_id=pn2.user_id and pn1.date>=pn2.date group by pn1.user_id,pn1.date) p1 on p1.user_id=login.user_id and p1.date=login.date join user on login.user_id=user.id order by login.date,user.name;
拼多多集团-PDD二面246人在聊
查看20道真题和解析
投递蚂蚁集团等公司10个岗位 >