题解 | SQL253 平均工资

WITH t1 AS (
    SELECT
        salary,
        RANK() OVER(ORDER BY salary DESC) AS ranking_max,
        RANK() OVER(ORDER BY salary ASC) AS ranking_min
    FROM salaries
    WHERE to_date = '9999-01-01'
),
t2 AS (
    SELECT
        AVG(salary) AS avg_salary
    FROM t1
    WHERE ranking_max != 1 AND ranking_min != 1
)
SELECT * FROM t2;]

# 注意排序条件均不能为 1，使用 and！