原题连接:
/practice/c1472daba75d4635b7f8540b837cc719?tpId=82&&tqId=29770&rp=1&ru=/ta/sql&qru=/ta/sql/question-ranking
此题有点意思,不让用 order by 函数,那么只能多重嵌套查值了,从里往外看,最里层的 select 是选择出最大的薪水,外面的 select 是选择出第二大的薪水,再让薪水等于第二大薪水即可,此题若要选第三大,只需再嵌套一层循环即可
select e.emp_no, salary, last_name, first_namefrom employees e inner join salaries s on e.emp_no = s.emp_nowhere to_date = '9999-01-01' and salary = (select max(salary) from salaries where salary < (select max(salary) from salaries))