首页 > 试题广场 >

平均工资

[编程题]平均工资

热度指数：172114 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 32M，其他语言64M
算法知识视频讲解

查找排除在职(to_date = '9999-01-01' )员工的最大、最小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

示例1

输入

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

输出

73292.000

算法知识视频讲解

MiSt201906201610370

发表于 2021-01-06 17:19:42

使用聚合函数，不用子查询COUNT(1) 代表所有数据长度， -2 代表减去最大最小值的两个长度 SELECT (SUM(salary) - MAX(salary) - MIN(salary)) / (COUNT(1)-2) avg_salary FROM salaries where to_ 展开全文

牛客307125317号

发表于 2020-07-16 17:47:02

select avg(a.salary) avg_salaryfrom salaries awhere a.to_date='9999-01-01'and a.salary not in (select max(b.salary) from salaries b where b.to_date='9 展开全文

我不是跳跳虎

发表于 2021-07-27 22:45:46

SQL 第54题解法一selectavg(salary)from salarieswhere salary not in ( select min(salary) from salaries where to_date = '9999-01-01')and salary n 展开全文

穷穷猪🐷

发表于 2020-10-06 15:42:23

查找排除最大、最小salary之后的当前(to_date = '9999-01-01' )员工的平均工资avg_salary 查找排除最大、最小salary之后的当前(to_date = '9999-01-01' )员工的平均工资avg_salary。 CREATE TABLE `salaries` 展开全文

wwww_w

发表于 2021-05-19 16:05:14

利用窗口函数 SELECT AVG(salary) FROM ( SELECT *, RANK() OVER(ORDER BY salary) r1, RANK() OVER(ORDER BY salary DESC) r2 FROM salaries WH 展开全文

·_______·

发表于 2021-02-05 17:58:47

思路是先求出最大和最小薪水值，用over函数变成两列，之后再从这个结果集中取出来薪水不是最大最小的即可 select avg(salary) avg_salary from (select salary ,max(salary) over() max_salary ,min(salary) ove 展开全文

Ding_123

发表于 2021-12-03 19:24:46

知识点排除在职的最大值和最小值就是salary大于最小值和salary小于最大值 where后面不能跟聚合函数，所以需要建立一个子查询代码 select avg(salary) as avg_salary from salaries where to_date = '9999-01-01' 展开全文