题解 | #查找排除当前最大、最小salary之后的员工的平均工资avg_salary#

本质上为排序，但也存在不涉及排序的简单操作方法
方法一 简便算法

SELECT (SUM(salary)-MAX(salary)-MIN(salary)) / (COUNT(salary)-2)
AS avg_salary
FROM salaries
WHERE to_date = '9999-01-01' 

方法二 实际思路

SELECT AVG(salary)
AS avg_salary
FROM salaries
WHERE salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01' )
    and salary NOT IN (SELECT MIN(salary) FROM salaries WHERE to_date = '9999-01-01' )
    and to_date = '9999-01-01'

方法三 RANK() OVER()函数的使用

SELECT AVG(salary)
FROM(
    SELECT *
    RANK() OVER(ORDER BY salary) AS r1
    RANK() OVER(ORDER BY salary DESC) AS r2
    WHERE to_date = '9999-01-01'
) 
AS a
WHERE r1 != 1
    AND r2 != 1