数据库SQL实战19_查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01‘ )员工的平均工资avg_salary。（where后面不能跟分组函数）

题目描述

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

CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
# 如：
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');

我的解答

select avg(salary) as avg_salary
from salaries
where to_date='9999-01-01'
and emp_no in (
    select emp_no from salaries
    where salary>min(salary) and salary<max(salary)
    and to_date='9999-01-01'
)

错误

因为：where 后面不能跟分组函数，因为执行顺序是先执行 where之后才执行 分组

还有，没有分组的时候，无法运行min,max,avg,sum,count等聚合函数

解决办法

将where后面的分组函数改为子查询

解答

select avg(salary) as avg_salary
from salaries
where to_date='9999-01-01'
and salary<>(select min(salary) from salaries where to_date='9999-01-01')
and salary<>(select max(salary) from salaries where to_date='9999-01-01')

或者

SELECT AVG(salary) AS avg_salary FROM salaries 
WHERE to_date = '9999-01-01' 
AND 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')

注意：这里子查询的to_date='9999-01-01’一定要加上的，否则OJ不通过