Score
0
Watch 44 Star 256 Fork 69

xuthus / 数据库SQL实战SQLMIT

Create your Gitee Account
Explore and code with more than 6 million developers,Free private repositories !:)
Sign up
Clone or download
60.统计salary的累计和running_total.md 1.18 KB
Copy Edit Web IDE Raw Blame History
xuthus authored 2019-08-12 23:23 . add:all

统计salary的累计和running_total

题目描述

按照salary的累计和running_total,其中running_total为前两个员工的salary累计和,其他以此类推。 具体结果如下Demo展示。

CREATE TABLE `salaries` ( 
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

答案

select s2.emp_no,s2.salary,sum(s1.salary) running_total from (select emp_no,salary from salaries where to_date = '9999-01-01') s1 inner join (select emp_no,salary from salaries where to_date = '9999-01-01') s2 on s1.emp_no <= s2.emp_no group by s2.emp_no

题解

1、获得当前员工的工资情况s1

select emp_no,salary from salaries where to_date = '9999-01-01'

2、连接当前员工信息s2,筛选出emp_no小于当前用户的所有员工信息

s1 inner join (select emp_no,salary from salaries where to_date = '9999-01-01') s2 on s1.emp_no <= s2.emp_no

3、根据emp_no进行分组,对salary列进行求和

select s2.emp_no,s2.salary,sum(s1.salary) running_total from s1 inner join s2 on s1.emp_no <= s2.emp_no group by s2.emp_no

Comment ( 0 )

Sign in for post a comment