叠加报表

最新推荐文章于 2021-03-18 14:52:19 发布

原创最新推荐文章于 2021-03-18 14:52:19 发布 · 307 阅读

0 ·

CC 4.0 BY-SA版权

本文通过自连接的方式展示了如何对员工薪资数据进行分组汇总，并进一步对比不同月份间同一员工的薪资变化情况，同时计算了每个月的最大薪资及累积薪资。

username month salary

A 2015-01 15
B 2015-01 5
A 2015-01 8
B 2015-01 25
A 2015-01 5
A 2015-02 4
A 2015-02 6
B 2015-02 10
B 2015-02 5

中间表（自连接）

A 2015-01 33 A 2015-01 33
A 2015-01 33 A 2015-02 10
A 2015-02 10 A 2015-01 33
A 2015-02 10 A 2015-02 10
B 2015-01 30 B 2015-01 30
B 2015-01 30 B 2015-02 15
B 2015-02 15 B 2015-01 30
B 2015-02 15 B 2015-02 15

select A.*,B.* from
(select username,month,sum(salary) as salary from sala group by username,month )A
inner join
(select username,month,sum(salary) as salary from sala group by username,month)B
on
A.username=B.username

un month sa sum

A 2015-01 33 33
A 2015-02 10 43
B 2015-01 30 30
B 2015-02 15 45

select A.username,A.month,max(A.salary) as salary,sum(B.salary) as accumulate
from
(select username,month,sum(salary) as salary from sala group by username,month) A
inner join
(select username,month,sum(salary) as salary from sala group by username,month) B
on
A.username=B.username
where B.month <= A.month
group by A.username,A.month
order by A.username,A.month;