## xuthus / 数据库SQL实战SQLMIT

Explore and code with more than 6 million developers，Free private repositories ！：）
21.查找所有员工自入职以来的薪水涨幅情况.md 1.38 KB
xuthus authored 2019-08-03 23:19 . add:22-25

# 查找所有员工自入职以来的薪水涨幅情况

## 题目描述

```CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
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 end.emp_no,(end.salary - start.salary) growth from (select emp_no,salary from salaries where to_date = '9999-01-01') end left join (select s.emp_no,salary from salaries s inner join employees e on s.emp_no = e.emp_no where s.from_date = e.hire_date) start on start.emp_no = end.emp_no order by growth`

## 题解

1、获得当前工资表，放入临时表end中

```-- 目前的工资
(select emp_no,salary from salaries where to_date = '9999-01-01') end```

2、获得第一次入职时的工资，放入临时表start中

```-- 最开始的工资
(select s.emp_no,salary from salaries s left join employees e on s.emp_no = e.emp_no where s.from_date = e.hire_date) start```

3、连接临时表，用end工资-start工资即可