题解 | #牛客每个人最近的登录日期(六)#

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

方案1：联结passing_number,user表+窗口函数

SELECT u.name as u_n,date,sum(p.number) over (partition by p.user_id order by p.date ASC) AS ps_num
FROM passing_number p
INNER JOIN user u
ON p.user_id = u.id
GROUP BY p.date,p.user_id
ORDER BY p.date,u.name

方案2：passing_number笛卡尔自连接+case 联结user表

select u.name as u_n,p1.date,sum(case when p1.date>=p2.date then p2.number else 0 end) as ps_num
FROM passing_number p1 cross join passing_number p2
ON p1.user_id=p2.user_id
inner join user u
on p1.user_id = u.id
group by p1.user_id,p1.date
order by p1.date,u_n;