SQL8

题目描述: 查找所有员工自入职以来的薪水涨幅情况(to_date='9999-01-01') 给出员工编号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 COMMENT '入职时间',
PRIMARY KEY (`emp_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL COMMENT '员工编号',
`salary` int(11) NOT NULL COMMENT '薪水',
`from_date` date NOT NULL COMMENT '入职时间',
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

本题思路是先分别用两次LEFT JOIN左连接employees与salaries，建立两张表，分别存放员工当前工资（CurTable） 与员工入职时的工资（StaTable），再用INNER JOIN连接上述两表，然后限定在同一员工下用当前工资减去入职工资， 得到的是对应的员工的薪水涨幅记录，最后再对

关键点：内层用LEFT JOIN，外层用INNER JOIN

select CurTable.emp_no,(CurTable.salary-StaTable.salary) as growth
from
(select  e1.emp_no, s1.salary from employees e1 left join salaries s1 on
 e1.emp_no=s1.emp_no and s1.to_date='9999-01-01') as CurTable
inner join 
(select  e2.emp_no, s2.salary from employees e2 left join salaries s2 on
 e2.emp_no=s2.emp_no and e2.hire_date=s2.from_date ) as StaTable
on CurTable.emp_no=StaTable.emp_no order by growth