题解 | 平均工资

with
t1 as(
    select
        min(salary) as exp
    from
        salaries
    where
        to_date='9999-01-01'
),
t2 as(
    select
        max(salary) as exp
    from
        salaries
    where
        to_date='9999-01-01'
),
t3 as(
    select * from t1
    union
    select * from t2
),
t4 as(
    select
        avg(salary) as avg_salary
    from
        salaries left join t3 on salaries.salary=t3.exp
    where
        exp is null
        and
        to_date='9999-01-01'
)
select * from t4