SQL实战54.查找排除当前最大、最小salary之后的员工的平均工资avg_salary

题目

解答

select avg(salary) as 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' 

select avg(salary) as avg_salary
from(
   select salary
          ,row_number() over (order by salary) as ranking
          ,row_number() over (order by salary desc) as ranks
   from salaries 
   where to_date = '9999-01-01') a 
where a.ranking !=1
and   a.ranks != 1;