[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'
                )
            )
        )
    )
)