# 子查询排序limit取第二id

# select s.emp_no, salary, last_name, first_name

# from employees e, salaries s

# where salary = (

# select salary

# from salaries

# group by salary

# order by salary desc

# limit 1 offset 1

# ) and e.emo_no = s.emp_no;

# 同上

# SELECT s.emp_no, s.salary, e.last_name, e.first_name

# FROM emp_no e

# JOIN salaries s ON e.emp_no = s.emp_no

# WHERE s.salary = (

# SELECT salary

# FROM salaries

# GROUP BY salary

# ORDER BY salary DESC

# LIMIT 1 OFFSET 1

# );

# 子查询排除最大值再取最大值id

select s.emp_no, salary, last_name, first_name

from employees e, salaries s

where salary = (

select max(salary)

from salaries

where salary <> (

select max(salary)

from salaries

)

) and e.emp_no = s.emp_no;

# 子查询小于最大值再取最大值

# select s.emp_no, salary, last_name, first_name

# from employees e, salaries s

# where salary = (

# select max(salary)

# from salaries

# where salary < (

# select max(salary)

# from salaries

# )

# ) and e.emp_no = s.emp_no;

# 子查询，salaries表自连接查询，取满足条件的记录

# select s.emp_no, s.salary, e.last_name, e.first_name

# from salaries s join employees e

# on s.emp_no = e.emp_no

# where s.salary = (

# select s1.salary

# from salaries s1 join salaries s2

# on s1.salary <= s2.salary

# group by s1.salary

# having count(distinct s2.salary) = 2

# );