查找排除当前最大、最小salary之后的员工的平均工资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`)); 输出格式:
avg_salary69462.5555555556 select avg(salary) as avg_salary from salaries where salary not in ( select max(salary) from salaries ) and salary not in ( select min(salary) from salaries ) and to_date = '9999-01-01';感觉这道题的逻辑有问题，上面的运行可以通过，但是子查询中没有加上to_date = '9999-01-01'的限制，这样一来，子查询找那个查到的最大最小salary是整个表的，而不是当前的。但是如果子查询中加上了to_date的限制，运行不通过。但我依然认为下面的才是真正正确的。
select avg(salary) as avg_salary from salaries where 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' ) and to_date = '9999-01-01';
