SQL 计算平均值时去掉最大值和最小值|极客教程

SQL 计算平均值时去掉最大值和最小值，你想计算平均值，但又想去掉最大值和最小值，以降低它们对最终计算结果的影响。例如，你希望先去掉最高工资和最低工资后，再计算全体员工的平均工资。

SQL 计算平均值时去掉最大值和最小值问题描述

你想计算平均值，但又想去掉最大值和最小值，以降低它们对最终计算结果的影响。例如，你希望先去掉最高工资和最低工资后，再计算全体员工的平均工资。

SQL 计算平均值时去掉最大值和最小值解决方案

MySQL 和 PostgreSQL
使用子查询去掉最大值和最小值。

1  select avg(sal)
2    from emp
3   where sal not in (
4      (select min(sal) from emp),
5      (select max(sal) from emp)
6   )

DB2、Oracle 和 SQL Server
使用内嵌视图以及窗口函数 MAX OVER 和 MIN OVER 来生成结果集，可以很容易去掉最大值和最小值。

1  select avg(sal)
2    from (
3  select sal, min(sal) over()min_sal, max(sal)over() max_sal
4    from emp
5         ) x
6   where sal not in (min_sal,max_sal)

SQL 计算平均值时去掉最大值和最小值扩展知识

MySQL 和 PostgreSQL
子查询能找到最高和最低的工资值。针对它们使用 NOT IN 之后，在计算平均值时就能去掉这些值。注意，如果有重复值（例如，最高或者最低工资对应的员工不止一人），那么所有重复值都将被过滤掉。如果希望只去掉一个最大值和一个最小值，只需要把它们从合计值里先减掉，再做除法即可。

select (sum(sal)-min(sal)-max(sal))/(count(*)-2)
  from emp

DB2、Oracle 和 SQL Server
内嵌视图 X 会返回全部工资值以及最高和最低的工资值。

select sal, min(sal)over() min_sal, max(sal)over() max_sal
  from emp
　
      SAL   MIN_SAL   MAX_SAL
--------- --------- ---------
      800       800      5000
     1600       800      5000
     1250       800      5000
     2975       800      5000
     1250       800      5000
     2850       800      5000
     2450       800      5000
     3000       800      5000
     5000       800      5000
     1500       800      5000
     1100       800      5000
      950       800      5000
     3000       800      5000
     1300       800      5000

在以上的查询结果中，每一行都包含最高和最低的工资值，因而查找最大值和最小值就不是问题。外层查询针对内嵌视图 X 返回的行又做了一次过滤，这样一来，计算平均值之前就先去掉了与 MIN_SAL 或者 MAX_SAL 相等的工资值。