题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#

WITH table1 AS
(SELECT
    employees.emp_no,
    salaries.salary,
    employees.first_name,
    employees.last_name
FROM
    employees
JOIN
    salaries
ON
    employees.emp_no=salaries.emp_no)


SELECT
    table1.emp_no,
    table1.salary,
    table1.last_name,
    table1.first_name
FROM
    (
    SELECT
        max(salary) as maxS
    FROM
        table1
    WHERE
        salary not in
        (
        SELECT 
            max(salary)
        FROM
            table1
        )
    )r
JOIN
    table1
ON
    table1.salary=r.maxS