题解 | #统计salary的累计和running_total#

【思路一】使用窗口函数
select emp_no, salary, sum(salary) over(order by emp_no) as running_total from salaries where to_date ='9999-01-01
【思路二】参照评论区大佬的写法，逐条比较计算running_total
SELECT s1.emp_no, s1.salary,
(SELECT SUM(s2.salary)
FROM salaries AS s2
WHERE s2.emp_no <= s1.emp_no
AND s2.to_date = '9999-01-01') AS running_total
FROM salaries AS s1
WHERE s1.to_date = '9999-01-01'
ORDER BY s1.emp_no
【思路三】先进行表连接再用 group by
select s.emp_no, s.salary, t.running_total from salaries as s left join
(select s1.emp_no, sum(s2.salary) as running_total from
(select emp_no, salary from salaries where to_date='9999-01-01' ) as s1
left join
(select emp_no, salary from salaries where to_date='9999-01-01' ) as s2
on s1.emp_no >=s2.emp_no
group by s1.emp_no) as t
on s.emp_no = t.emp_no
where to_date = '9999-01-01'