SQL54 平均工资

1. 题目

• 题目来源：SQL54 平均工资

• 题目描述
  查找排除在职(to_date = ‘9999-01-01’ )员工的最大、最小salary之后，其他的在职员工的平均工资avg_salary。

• 一张表

  10001|85097.000|2001-06-22|2002-06-22
  10001|88958.000|2002-06-22|9999-01-01
  10002|72527.000|2001-08-02|9999-01-01
  10003|43699.000|2000-12-01|2001-12-01
  10003|43311.000|2001-12-01|9999-01-01
  10004|70698.000|2000-11-27|2001-11-27
  10004|74057.000|2001-11-27|9999-01-01
  

• 所需结果

  avg_salary
  73292 
  

2. 题解

只需要求解 salary 字段的平均值，但是需要过滤掉在职员工的最大最小值。
思路：
使用子查询，对条件进行行过滤，有两种方法：
① 使用最值聚合函数：MAX() 和 MIN()
② 使用最值窗口函数：MAX() OVER() 和 MIN() OVER()

# 方法一：WHERE后子查询
SELECT AVG(salary) avg_salary
FROM salaries
WHERE to_date = '9999-01-01'
      AND salary != (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
      AND salary != (SELECT MIN(salary) FROM salaries WHERE to_date = '9999-01-01')
      

# 方法二：FROM后子查询
SELECT AVG(salary) avg_salary
FROM (SELECT *,
             MAX(salary) OVER() max_salary, 
             MIN(salary) OVER() min_salary
      FROM salaries
      WHERE to_date = '9999-01-01'
     ) t
WHERE to_date = '9999-01-01'
      AND salary != t.max_salary
      AND salary != t.min_salary

需要注意前置条件【在职员工】，是在职员工的最大最小值。