SQL54 查找排除当前最大、最小salary之后的员工的平均工资avg_salary

关注

原创最新推荐文章于 2026-08-11 10:23:15 发布 274 阅读 AI 写同款· GEO 优化 ›

牛客 数据库SQL实战查找排除当前最大、最小salary之后的员工的平均工资avg_salary 题目描述:\color{blue}题目描述:题目描述: 查找排除当前最大、最小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, P... 阅读详情

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

drop table if exists  `salaries` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` float(11,3) 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练习题54（中等）：

SELECT avg(a.salary)
from salaries a
where a.to_date='9999-01-01'
and a.salary not in (
    select max(b.salary)--计算最大值
    from salaries b
    where b.to_date='9999-01-01')
and a.salary not in (
    select min(b.salary)--计算最小值
    from salaries b
    where b.to_date='9999-01-01');