题解 | #查找排除当前最大、最小salary之后的员工的平均工资avg_salary#
查找排除当前最大、最小salary之后的员工的平均工资avg_salary
http://www.nowcoder.com/practice/95078e5e1fba4438b85d9f11240bc591
第一个方法先排除再求平均值
SELECT 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')
;
/*
1.先用where语句查找to_date = '9999-01-01'的员工中排除最大、最小salary
2.再用where取to_date = '9999-01-01' 剩下员工的平均值
*/
第二个方法
先找出to_date = '9999-01-01'，求和后减去一个最大值和一个最小值，
再除以员工总数-2.
SELECT (sum(salary)-max(salary)-min(salary))/(count(salary)-2) avg_salary
FROM salaries
WHERE to_date='9999-01-01';
