搜索内容

有一个问题?

如果您有任何疑问,可以在下面询问或输入您要寻找的!

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

生成海报
Dream丶Killer
Dream丶Killer 2021-02-09 22:39
阅读需:0

SQL训练54:搜索清除当今较大 、最少salary以后的职工的平均收入avg_salary

题型连接:牛客网

题型叙述
搜索清除较大 、最少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');

輸出文件格式:

avg_salary
73292

打法
答题观念便是想办法除去较大 薪水和最少薪水,这儿应用子查询的方法,要留意一点的是题中少了一个标准,便是要限制to_date = '9999-01-01',表明获得当今数据信息。

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')
评论
  • 消灭零回复