问题描述
查找排除最大、最小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
));
Sql语句
错误写法
select avg(salary) as avg_salary
from salaries
where salary>min(salary) and salary<max(salary)
and to_date='9999-01-01'
错误原因:where 后面不能跟分组函数,因为执行顺序是先执行where 之后才执行分组,还没有分组的时候是无法运行min,max,avg,sum,count这类函数的!
嵌套查询
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');