本文主要是介绍SQL215 查找在职员工自入职以来的薪水涨幅情况，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
输入：
drop table if exists `employees` ;
drop table if exists `salaries` ;
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`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','2001-06-22');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1999-08-03');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');输出：
10001|3861
select t1.emp_no emp_no, (t1.salary-t2.salary) growth
from(select * from salaries s where s.to_date='9999-01-01') t1 #当前工资join(select e.emp_no emp_no, s.salary salary from employees e join salaries s on e.emp_no = s.emp_no and e.hire_date = s.from_date)t2 #入职工资on t1.emp_no = t2.emp_no
order by growth
建立两张表：一张是初始工资 from_date = hire_date
select s.emp_no , s.salary as FirstSalary from salaries s join employees e on s.from_date = e.hire_date;
(select s.emp_no , s.salary as FirstSalary from salaries s join employees e on s.from_date = e.hire_date) m1
另一张是 to_data=9999 把在职员工的编号、薪水查出来
select s2.emp_no, s2.salary as SecondSalary from salaries s2 where s2.to_date = '9999-01-01'
(select s2.emp_no, s2.salary as SecondSalary from salaries s2 where s2.to_date = '9999-01-01') m2
然后把两张表根据emp_no做一个连接，然后两个工资字段相减
select m1.emp_no ,m2.SecondSalary -m1.FirstSalary as growth
from m1
join m2
on m1.emp_no = m2.emp_no
order by growth;
这篇关于SQL215 查找在职员工自入职以来的薪水涨幅情况的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
