Mysql去掉最大最小值求平均值

Mysql去掉最大最小值求平均值

思路：

1.求出 最大值，最小值
2.剔除 最大最小值
3. 对剩余数求平均

select max(a) from t1;
select min(a) from t1;

select a from t1
where a not in (
(select max(a) from t1
(select min(a) from t1          
               );

select avg(a) from t1
where a not in (
(select max(a) from t1
(select min(a) from t1          
               );

题目：

查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary。

select avg(salary) from salaries
where salary not in (
(select max(salary) from salaries 
where to_date = '9999-01-01'),
(select min(salary) from salaries 
where to_date = '9999-01-01')             
               )
and to_date = '9999-01-01';