# SQL练习54：查找排除当前最大、最小salary之后的员工的平均工资avg_salary

Dream丶Killer 2021-02-09 22:39

### SQL训练54：搜索清除当今较大 、最少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`));

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

avg_salary
73292

``````SELECT AVG(salary) 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')
``````

• 消灭零回复

## 最新评论

• 01月26日

我是这篇文章的创作本人 请您把文章删了 ...