题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#

1.分析
两个方法
方法1 取两次最大值（不具备通用性）
①首先根据max函数找到最大的salary，然后在小于最大值的salary里面再找到一个最大值，即为第二大值。
②连结emploees表和salaries表，连接条件是emp_no相等，where筛选条件时salary等于刚才查询出来的第二大值。

方法2 找出排名第几的薪水
①首先是salaries表建立自联结（s2与s3），连接条件是日期相等，where筛选条件是s2.salary <= s3. salary
②然后对s2.salary分组

# SELECT e.emp_no, s1.salary, e.last_name, e.first_name
# FROM employees as e
# left join 
# salaries as s1
# on e.emp_no = s1.emp_no
# where s1.salary = (
#                      select max(s2.salary) from salaries as s2
#                      WHERE s2.salary < (select max(s3.salary) from salaries as s3)
#                   )



SELECT e.emp_no, s1.salary, e.last_name, e.first_name
FROM employees as e
inner join 
salaries as s1
on e.emp_no = s1.emp_no
and s1.salary = (
                     select s2.salary from salaries as s2
                     inner join 
                     salaries as s3
                     ON s1.to_date=s2.to_date
                     where s2.salary <= s3. salary
                     group by s2.salary
                     having count(distinct s3.salary) = 2
                  )