求解一道sql题目，感激不尽！

[kakkoii_moe]

题目：查找排除最大、最小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');

 

以下是我的答案，系统报错 SQL_ERROR_INFO: 'Invalid use of group function'

select avg(T1.salary)
from (select salary,to_date
     from salaries 
     where salary between max(salary) and min(salary)) as T1
where to_date = '9999-01-01'

 

[Sparrow-W]

where子句中是不能使用max、min之类的聚集函数作为条件表达式的。聚集函数只能用于select子句和group by中的having子句。这里需要用having子句来写

[2名名名名名名名名名名名名名名名名名名名]

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')

换个支持的

[2名名名名名名名名名名名名名名名名名名名]

explain
select avg(t.salary2) from
(select case when salary = (select max(salary) from salaries where to_date = '9999-01-01') then 0
when salary = (select min(salary) from salaries where to_date = '9999-01-01') then 0 else salary
end as salary2
from salaries a
where to_date = '9999-01-01') t

finghting

[往事随风_h]

自己的作业自己做

[无远开发平台]

很奇怪你写的SQL中并没有group，为啥报这个错误？你的字段名 to_date  是不是跟sql保留函数 to_date 冲突了 导致解析报错？换个名字比如 叫 end_date start_date 试试？

另外，为啥不直接用无远开发平台直接开发？

[布谷歌]

如下：

SELECT AVG(salary)
FROM salaries a JOIN (SELECT to_date, MAX(salary) AS mx,MIN(salary) AS mi FROM salaries WHERE to_date='9999-01-01') b
ON a.`to_date`=b.to_date AND a.`salary`>mi AND a.`salary`<mx