WITH t1 AS (
-- 确保登录就会刷题
SELECT *
FROM login
WHERE (user_id,date)
IN(SELECT user_id,date FROM passing_number)
),
t2 AS (
SELECT
user_id,
date,
SUM(number) AS ps_num
FROM passing_number
GROUP BY user_id,date
),
t3 AS (
SELECT
u.name AS u_n,
t2.date,
t2.ps_num
FROM t2
LEFT JOIN user u
ON u.id = t2.user_id
ORDER BY t2.date asc,u.name ASC
),
t4 AS (
SELECT
u_n,
date,
SUM(ps_num) OVER(PARTITION BY u_n ORDER BY date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM t3
)
SELECT * FROM t4;
# 这道题的关键在于累加值，累加值不纠结需要group by！
查看27道真题和解析
