思想:
题目要求获取当前(to_date='9999-01-01')薪水第二多的员工的emp_no以及其对应的薪水salary。首先通过条件to_date = '9999-01-01'获取当前薪水,其次通过条件order by salary desc limit 1,1找到薪水第二多的员工(前提条件当前薪水没有重复的)。
select emp_no,salary from salaries where to_date = '9999-01-01' order by salary desc limit 1,1;
下面是改进的程序。首先通过子查询找到当前第二的薪水select distinct salary from salaries where to_date='9999-01-01' order by salary desc limit 1,1(通过distinct取没有重复的当前薪水,然后按照降序排序,最后取出当前第二的薪水)。其次通过条件salary=子查询找出当前第二工资的员工信息。
select emp_no, salary from salaries
where to_date = '9999-01-01' and
salary = (select distinct salary from salaries where to_date='9999-01-01' order by salary desc limit 1,1);