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

方式一 使用两个子查询 not in
select avg(salary) from salaries
where salary not in
((select min(salary) from salaries),
(select max(salary) from salaries)) and to_date='9999-01-01'
方式二 两个子查询+过滤条件
select avg(salary) from salaries
where salary != (select min(salary) from salaries where to_date = '9999-01-01')
and salary != (select max(salary) from salaries where to_date = '9999-01-01')
and to_date = '9999-01-01'
排除的是当前的最大值和最小值，注意添加日期这个过滤条件
纠正一
这样子直接查询是没问题的，但作为子查询就不行，有两列值，子查询只允许一列,那就分开查询然后用not in
SELECT MAX(salary),MIN(salary) FROM salaries
​