## xuthus / 数据库SQL实战SQLMIT

Create your Gitee Account
Explore and code with more than 6 million developers，Free private repositories ！：）
60.统计salary的累计和running_total.md 1.18 KB
xuthus authored 2019-08-12 23:23 . add:all

# 统计salary的累计和running_total

## 题目描述

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