解法一 笛卡尔积
1、计算每个用户截至到当天的累积刷题数量
select n1.user_id,n1.date,sum(n2.number) as ps_num from passing_number n1,passing_number n2 where n1.date>=n2.date and n1.user_id=n2.user_id group by n1.date,n1.user_id2、与user表连接,查询结果信息
select b.name as u_n,a.date,a.ps_num from (select n1.user_id,n1.date,sum(n2.number) as ps_num from passing_number n1,passing_number n2 where n1.date>=n2.date and n1.user_id=n2.user_id group by n1.date,n1.user_id) as a join user as b on a.user_id=b.id order by a.date,b.name;
解法二 sum() over() 窗口函数,累计求和
SELECT name AS u_n,date, SUM(number) OVER (PARTITION BY user_id ORDER BY date) AS ps_num FROM passing_number AS p JOIN user ON user.id=p.user_id ORDER BY date,name;