题解 | #平均工资#

select
#	s.emp_no #✖，没有group by的时候，select里也不能使用非聚合函数；where中不能用聚合函数
    avg(s.salary) as avg_salary
from
    salaries s
where
    s.to_date = '9999-01-01'
    and s.salary not in (
        select
            max(a.salary)
        from
            salaries a
        where
            a.to_date = '9999-01-01'
    )
    and s.salary not in (
        select
            min(b.salary)
        from
            salaries b
        where
            b.to_date = '9999-01-01'
    )