题解 | #平均工资#

思路：按照题意使用 MAX/MIN + NOT IN 去除掉最大最小salary，然后算平均值。

1. 查询在职员工的最大salary

select max(salary)
from salaries
where to_date = '9999-01-01'

2. 查询在职员工的最小salary

select min(salary)
from salaries
where to_date = '9999-01-01'

3. 查询在职员工的平均salary

select avg(salary)
from salaries
where to_date = '9999-01-01'
and salary not in (select max(salary) from salaries where to_date = '9999-01-01')
and salary not in (select min(salary) from salaries where to_date = '9999-01-01')