题解 | #统计出当前各个title类型对应的员工当前薪水对应的平均工资#

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

方法一 多层max嵌套

SELECT
    e.emp_no,
    s.salary,
    e.last_name,
    e.first_name 
FROM
    employees AS e
    INNER JOIN salaries AS s ON e.emp_no = s.emp_no 
WHERE
    s.salary = (
    SELECT
        max( salary ) 
    FROM
    ( SELECT salary FROM salaries AS s1 WHERE s1.salary < ( SELECT max( salary ) FROM salaries ) ) AS m 
    )

方法二 通用型求任意第几高

SELECT
    e.emp_no,
    s.salary,
    e.last_name,
    e.first_name 
FROM
    employees e
    JOIN salaries s ON e.emp_no = s.emp_no 
    AND s.salary = (
    SELECT    # 选出第二大的值
        * 
    FROM    # 连接时每一项s2的值会对应满足条件的s1的值
        salaries s1  # 所以s2所对应的值的行数就表示有几条小于等于该工资
        JOIN salaries s2 ON s1.salary <= s2.salary   # 第二大就对应两行
    GROUP BY
        s1.salary  # 要用s1的工资来分类才能统计出s2对应的条数，数的本来就是s1的行数
    HAVING
        count( DISTINCT s2.salary )= 2  # 考虑到有重复的工资，故要用distinct
    )