牛客sql：不用orderby取薪水第二多的员工信息

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

CREATE TABLEemployees (

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 TABLEsalaries (

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));

#子查询的自连接，根据s1.s分组，取满足条件的s1.s。#应满足的条件是：大于等于s1.s的s2.s的数目为2#通用，若是取薪水第三/四大的也可。select e.emp_no,s.salary,e.last_name,e.first_namefrom employees ejoin salaries son e.emp_no=s.emp_noand s.to_date='9999-01-01'and s.salary=(select s1.salaryfrom salaries s1join salaries s2on s1.salary<= s2.salaryand s1.to_date='9999-01-01'and s2.to_date='9999-01-01'groupby s1.salaryhavingcount(distinct s2.salary)=2)