SQL求截至到某个时间用户的刷题数量

数据

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 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');

使用窗口函数进行组内相加

select user_id, date, sum(number) over (partition by user_id order by date asc) pass_num
from passing_number

连接user表得到名字

select name,date,pass_num 
from 
	user 
INNER JOIN
	(select user_id, date, sum(number) over (partition by user_id order by date asc) pass_num
	from passing_number) tmp
on 
	tmp.user_id=user.id
ORDER BY date ASC,name ASC

链接

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