每天五道MySQL---4

1. 获取当前（to_date='9999-01-01'）薪水第二多的员工的emp_no以及其对应的薪水salary

   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 emp_no, salary 
   from salaries
   where to_date='9999-01-01'
   order by salary desc limit 1 offset 1
   

2. 查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by

   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 s.emp_no,Max(s.salary),e.last_name,e.first_name
   from employees e join salaries s
   on e.emp_no = s.emp_no
   where s.to_date='9999-01-01'
   and s.salary not in (
       select Max(salary) from salaries where s.to_date='9999-01-01'
   )
   

3. 查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

   CREATE TABLE `departments` (
   `dept_no` char(4) NOT NULL,
   `dept_name` varchar(40) NOT NULL,
   PRIMARY KEY (`dept_no`));
   CREATE TABLE `dept_emp` (
   `emp_no` int(11) NOT NULL,
   `dept_no` char(4) NOT NULL,
   `from_date` date NOT NULL,
   `to_date` date NOT NULL,
   PRIMARY KEY (`emp_no`,`dept_no`));
   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`));
   ------------------------------------
   select e.last_name,e.first_name,de.dept_name
   from employees e
   left join dept_emp d 
   on d.emp_no = e.emp_no
   left join departments de 
   on de.dept_no = d.dept_no
   

4. 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

   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 max(salary) - min(salary) as growth
   from salaries
   where emp_no = 10001
   

5. 查找所有员工自入职以来的薪水涨幅情况，给出员工编号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 now.emp_no,(now.salary-th.salary)growth 
   from (select emp_no, salary 
         from salaries 
         where to_date='9999-01-01') now inner join 
         (select e.emp_no, s.salary 
          from employees e ,salaries s 
          where e.emp_no=s.emp_no and e.hire_date=s.from_date) th 
   where now.emp_no=th.emp_no order by growth