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

题目描述

查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by
CREATE TABLE employees (
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 TABLE salaries (
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));

解题思路

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

AC代码

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