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
21.查找所有员工自入职以来的薪水涨幅情况.md 1.38 KB
Copy Edit Web IDE Raw Blame History
xuthus authored 2019-08-03 23:19 . add:22-25

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

题目描述

查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序

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工资即可

Comment ( 0 )

Sign in for post a comment