题解 | 平均工资

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

条件一：排除在职(to_date = '9999-01-01' )员工的最大、最小salary之后

salary <> (select max(salary) from salaries where to_date = '9999-01-01') and salary <>(select min(salary) from salaries where to_date = '9999-01-01')

条件二：其他的在职9999-01-01在职员工的平均工资avg_salary。

to_date = '9999-01-01'

用salary去判断，可以防止最大最小salary有重复