统计牛客每个用户刷题信息

两种方法：

• 使用窗口函数SUM，将日期作为“窗口”计算累加刷题数量
• 使用自联结

窗口函数

SELECT name AS u_n,date,
SUM(number) OVER (PARTITION BY user_id ORDER BY date) AS ps_num
FROM passing_number AS p
INNER JOIN user
ON user.id=p.user_id
GROUP BY date,u_n
ORDER BY date,name

使用自联结

SELECT name AS u_n,date,ps_num
FROM (
    SELECT p1.user_id,p1.date,SUM(p2.number)AS ps_num
    FROM  passing_number AS p1,passing_number AS p2
    WHERE p1.date>=p2.date
    AND p1.user_id=p2.user_id
    GROUP BY p1.date,p1.user_id
    ) AS p
INNER JOIN user
ON user.id=p.user_id
GROUP BY date,u_n
ORDER BY date,name

遇到这类问题，使用窗口函数最方便，代码也比较简洁