不开窗求每个人当日累计的答题数

SQL265 牛客每个人最近的登录日期(六)

• 题目
• 题解(68)
• 讨论(192)
• 排行
• 面经

  new

较难  通过率：33.88%  时间限制：1秒  空间限制：32M

描述

牛客每天有很多人登录，请你统计一下牛客每个用户刷题情况，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。 不存在没有登录却刷题的情况，但存在登录了没刷题的情况，不会存在刷题表里面，会存在提交代码没有通过的情况并记录在刷题表里，通过数目是0。
有登录(login)记录表，简况如下:

id	user_id	client_id	date
1	2	1	2020-10-12
2	3	2	2020-10-12
3	1	2	2020-10-12
4	1	3	2020-10-13
5	3	2	2020-10-13

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网
......
第5行表示user_id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网


有刷题（passing_number)表，简况如下:

id	user_id	number	date
1	2	4	2020-10-12
2	3	1	2020-10-12
3	1	0	2020-10-13
4	3	2	2020-10-13

第1行表示user_id为2的用户在2020-10-12通过了4个题目。
......
第3行表示user_id为1的用户在2020-10-13提交了代码但是没有通过任何题目。
第4行表示user_id为3的用户在2020-10-13通过了2个题目


有用户(user)表，注：这里id字段对应user_id，简况如下:

id	name
1	tm
2	fh
3	wangchao

请你写出一个sql语句查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题，并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的哪一天的数据不需要输出，上面的例子查询结果如下:

u_n	date	ps_num
fh	2020-10-12	4
wangchao	2020-10-12	1
tm	2020-10-13	0
wangchao	2020-10-13	3

查询结果表明:
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

示例1

输入：

drop table if exists login;
drop table if exists passing_number;
drop table if exists user;
drop table if exists client;
CREATE TABLE `login` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`client_id` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

CREATE TABLE `passing_number` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`number` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

CREATE TABLE `user` (
`id` int(4) NOT NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`));


INSERT INTO login VALUES
(1,2,1,'2020-10-12'),
(2,3,2,'2020-10-12'),
(3,1,2,'2020-10-12'),
(4,1,3,'2020-10-13'),
(5,3,2,'2020-10-13');

INSERT INTO passing_number VALUES
(1,2,4,'2020-10-12'),
(2,3,1,'2020-10-12'),
(3,1,0,'2020-10-13'),
(4,3,2,'2020-10-13');

INSERT INTO user VALUES
(1,'tm'),
(2,'fh'),
(3,'wangchao');

复制输出：

fh|2020-10-12|4
wangchao|2020-10-12|1
tm|2020-10-13|0
wangchao|2020-10-13|3

开窗:

with t1 as (
select
user_id,number,date
    ,sum(number) over(partition by user_id order by date rows between unbounded preceding and current row) ps_num
from
passing_number
    )
    select
    user.name,t1.date,t1.ps_num
    from
    t1 join `user` 
    on t1.user_id = user.id
    order by `date`,name 

不开窗:

select
x2.name
,x.date 
,(select sum(x1.number) from passing_number x1  where x1.user_id =x.user_id and x1.date<= x.date ) as ps_num
from passing_number x
left join user x2 on x.user_id  = x2.id
order by x.date,x2.name