题解 | #平均工资#

# 先查询在职员工的最高工资和最低工资
select max(salary) max_s, min(salary) min_s
from salaries
where to_date = '9999-01-01'

# 查询排除最高工资和最低工资的员工平均工资
select avg(salary) avg_salary
from salaries
where salary <> (
    select max(salary) max_s
    from salaries
    where to_date = '9999-01-01'
)
and salary <> (
    select min(salary) min_s
    from salaries
    where to_date = '9999-01-01'
)
and to_date = '9999-01-01';