题目描述

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

CREATE TABLEemployees(

emp_noint(11) NOT NULL,

birth_datedate NOT NULL,

first_namevarchar(14) NOT NULL,

last_namevarchar(16) NOT NULL,

genderchar(1) NOT NULL,

hire_datedate NOT NULL,

PRIMARY KEY (emp_no));

CREATE TABLEsalaries(

emp_noint(11) NOT NULL,

salaryint(11) NOT NULL,

from_datedate NOT NULL,

to_datedate NOT NULL,

PRIMARY KEY (emp_no,from_date));

解题思路

表的连接时基础，之后的思路参考这篇博客的第一个思路

AC代码

SELECT s.emp_no, salary, last_name, first_name FROM salaries s, employees e WHERE (to_date = '9999-01-01' ANDe.emp_no = s.emp_no ANDsalary IN (SELECT MAX(salary) FROM salaries WHERE (to_date = '9999-01-01' ANDsalary NOT IN (SELECT MAX(salary) FROM salaries WHERE (to_date = '9999-01-01')))))

[SQL实战]之查找当前薪水排名第二多的员工编号emp_no 薪水salary last_name以及first_name 不准使用order by