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

问题描述

查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary。
CREATE TABLE salaries ( emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));

来源：牛客网
链接：https://www.nowcoder.com/practice/95078e5e1fba4438b85d9f11240bc591?tpId=82&&tqId=29822&rp=1&ru=/ta/sql&qru=/ta/sql/question-ranking

Sql语句

错误写法

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

错误原因：where 后面不能跟分组函数，因为执行顺序是先执行where 之后才执行分组，还没有分组的时候是无法运行min,max,avg，sum,count这类函数的！
嵌套查询

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