SQL217：找NO2，selcet max() + where不等于

select rk.emp_no,rk.salary,e.last_name,e.first_name 
from employees as e right join 
 (select emp_no, salary from salaries
 where salary =(select max(salary) from salaries 
 where salary != (select max(salary) from salaries))) as rk
 on  e.emp_no = rk.emp_no

select e.emp_no,s.salary,e.last_name,e.first_name 
from employees as e left join salaries as s 
on e.emp_no = s.emp_no
where salary =(select max(salary) from salaries 
where salary != (select max(salary) from salaries))

SQL217 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

1、逻辑：

系统答案：找工资NO.1值 → 工资NO.2值( ) → 子连接且工资=NO.2的

我的答案：找工资NO.1值 → 工资NO.2值(max+where!=) →工资NO.2的工号→ 子连接补齐信息

2、注意：

①where 后面不能用聚合函数，如where max(字段)是不行的

②select emp,max()...group by()...having max() 这样不是找公司max值，而是每个员工的max值