题解 | 平均工资

-- 首先，求出来在职员工的最大
WITH
    max_salary AS(
        SELECT MAX(salary) max_salary
        FROM salaries
        WHERE to_date = '9999-01-01'
    ),
-- 接着，求出最小
    min_salary AS(
        SELECT MIN(salary) min_salary
        FROM salaries
        WHERE to_date = '9999-01-01'
    )

-- 找出工资不在这个里面的在职员工的平均薪资
SELECT AVG(salary) avg_salary
FROM salaries
WHERE salary NOT IN(
    SELECT *
    FROM max_salary
)
AND to_date='9999-01-01'
AND salary NOT IN(
    SELECT *
    FROM min_salary
)

这个题目还可以用窗口函数的方法 但是更加复杂了