题目描述
牛客每天有很多人登录,请你统计一下牛客每个用户查询刷题信息,包括: 用户的名字,以及截止到某天,累计总共通过了多少题。 不存在没有登录却刷题的情况,但是存在登录了没刷题的情况,不会存在刷题表里面,有提交代码没有通过的情况,但是会记录在刷题表里,只不过通过数目是0。
有一个登录(login)记录表,简况如下:
第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网
。。。
第5行表示id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网
有一个刷题(passing_number)表,简况如下:
第1行表示id为2的用户在2020-10-12通过了4个题目。
。。。
第3行表示id为1的用户在2020-10-13提交了代码但是没有通过任何题目。
第4行表示id为4的用户在2020-10-13通过了2个题目
还有一个用户(user)表,简况如下:
请你写出一个sql语句查询刷题信息,包括: 用户的名字,以及截止到某天,累计总共通过了多少题,并且查询结果先按照日期升序排序,再按照姓名升序排序,有登录却没有刷题的哪一天的数据不需要输出,上面的例子查询结果如下:
查询结果表明:
fh在2020-10-12为止,总共通过了4道题,输出为4
wangchao在2020-10-12为止,总共通过了1道题,总计为1
tm在2020-10-12为止只登陆了没有刷题,故没有显示出来
tm在2020-10-13为止刷了题,但是却没有通过任何题目,总计为0
wangchao在2020-10-13通过2道,但是加上前面2020-10-12通过1道,故在2020-10-13为止总共通过了3道题,总计为3
思路:MySQL8.0之后提供了窗函数,我们可以采用over 很方便的解决该题,over的用法为:
over(partition by columnname1 order by columnname2)
含义:按columname1指定的字段进行分组排序,或者说按字段columnname1的值进行分组排序。
注:over不能单独使用,要和分析函数:rank(),dense_rank(),row_number()等一起使用。
select
a.name,
b.date,
sum(b.number) over(partition by b.user_id order by b.date) as ps_num
from
user as a
inner join
passing_number as b
on
a.id=b.user_id
order by
b.date,a.name
asc;