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

思路：
第一，获取当前员工的最大值max（salary）、最小值min（salary）；
第二，排除最大值、最小值后的salary取平均值avg（salary）。
eg.
查找排除最大、最小salary之后的当前(to_date = '9999-01-01' )员工的平均工资avg_salary。

方法一：

select avg(salary) as avg_salary
from salariesm
where to_date='9999-01-01'
and salary not in ( select max(salary)
                    from salariesm
                    where to_date='9999-01-01')
and salary not in(                        # 两个and
                    select min(salary)
                    from salariesm
                    where to_date='9999-01-01');

或换种形式：

select avg(salary) as avg_salary
from salariesm
where to_date='9999-01-01'
and salary not in ( select max(salary)
                    from salariesm
                    where to_date='9999-01-01'
                    union   # 通过union连接
                    select min(salary)
                    from salariesm
                    where to_date='9999-01-01');

或换种形式：

select avg(salary) as avg_salary
from salariesm
where to_date='9999-01-01'
and salary not in ( (select max(salary)
                    from salariesm
                    where to_date='9999-01-01'),    # ","通过逗号并列两个值
                    (select min(salary)
                    from salariesm
                    where to_date='9999-01-01'));

换种形式：

select
avg(salary) avg_salary
from
(select 
salary
,max(salary) over() max_salary
,min(salary) over() min_salary
from
salariesm
where to_date = '9999-01-01' ) as a    # 封装成一个表
where salary != max_salary and salary != min_salary;

方法二：

select ((sum(salary)-max(salary)-min(salary))/(count(salary)-2)) as avg_salary   # 直接通过计算逻辑进行计算
from salary
where to_date='9999-01-01';