题解 | 薪水第二多员工的emp_no及对应薪水salary

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

with t1 as (select emp_no,salary from salaries

where salary!=(

select max(salary) from salaries

)) #建立临时表t1,排除掉薪水最高的员工薪资信息

select t1.emp_no,salary,last_name,first_name from t1 left join employees e on t1.emp_no=e.emp_no

where salary =(

select max(salary) from t1

);

将t1表与员工信息表连接，筛选条件为员工工资为t1表中最大的（对应原表薪资第二高）