题解 | #平均工资#

# 找出在职员工的薪资
with t1 as (
    select emp_no, salary
    from salaries 
    where to_date = '9999-01-01'
),
# 找到最大，最小的工资是多少
t2 as (
    select max(salary) as max_salary, min(salary) as min_salary
    from t1
)
select AVG(salary) as avg_salary
from t1
where salary != (select max_salary from t2) 
and salary != (select min_salary from t2);