60.统计salary的累计和running_total.md · 柯基与佩奇/数据库SQL实战

# 统计salary的累计和running_total

## 题目描述

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

``` mysql
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` ));
```

## 答案

``` mysql
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

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

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

``` mysql
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列进行求和

``` mysql
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
```

柯基与佩奇/数据库SQL实战

About

Releases

Contributors

Activities