题解 | #查找排除当前最大、最小salary之后的员工的平均工资avg_salary#

题目不够严谨，在找最高工资和最低工资的时候也要加上to_data='9999-01-01'这个条件
不然系统过不了

select avg(t.salary) as avg_salary from salaries t where 
t.emp_no 
not in(select s.emp_no from salaries s where s.salary=(select min(s1.salary) from salaries s1 where s1.to_date='9999-01-01')
union all 
select s.emp_no from salaries s where s.salary=(select max(s1.salary) from salaries s1 where s1.to_date='9999-01-01' ))
and t.to_date='9999-01-01'