SQL实战71.牛客每个人最近的登录日期(六)

题目

解答

注意：增加条件算累和

select u.name as u_n,t.date,t.ps_num
from (select pn1.user_id,pn1.date,
(select sum(pn2.number) from passing_number as pn2  
where pn1.date >= pn2.date 
and pn1.user_id = pn2.user_id) as ps_num
from passing_number as pn1) as t       
left join user as u on t.user_id=u.id
order by date,u_n;

自己想法：

select u.name u_n,ps1.date,(select sum(ps2.number)
                            from passing_number as ps2
                            where ps2.user_id = ps1.user_id 
                            and ps2.date<=ps1.date) as ps_num
from passing_number as ps1 join user u on u.id = ps1.user_id
order by date,u_n;