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 )