首页 > 试题广场 >

平均工资

[编程题]平均工资

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

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

示例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

算法知识视频讲解

Sqlite

牛客740326194号

方案①

select (sum(salary)-max(salary)-min(salary))/(count(emp_no)-2) avg_salary

from salaries

where 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)
AND salary NOT IN (SELECT MIN(salary) FROM salaries)

——果果果子

发表于 2022-03-24 16:02:35 回复(0)

Sqlite

邓子谦

题目中没有规定去掉最大值和最小值的人数，如果有多名在职员工同时获得最大值或最小值的薪资时，下面这种写法就是去掉所有这些员工。

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

但是，如果想要的效果的去掉一个最大值，一个最小值，那么就需要用下面这种写法：

select (sum(salary) - min(salary) - max(salary))/(count(*)-2) from salaries
where to_date = '9999-01-01'

发表于 2022-03-11 17:30:28 回复(0)

Sqlite

不想上班的芭乐很喜欢唱跳ra

SELECT AVG(s3.salary) FROM salaries s3
where s3.to_date = '9999-01-01'
and s3.emp_no not in(
        SELECT emp_no FROM salaries
        WHERE (salary =(
        SELECT MAX(s1.salary)
        FROM salaries s1
        WHERE s1.to_date = '9999-01-01')
        or salary =(SELECT min(s2.salary)
        FROM salaries s2
        WHERE s2.to_date = '9999-01-01'))

)

这道题目有语病，

正确的题目：查找(to_date = '9999-01-01' )且排除有最大、最小salary的在职员工，其他员工的平均工资是多少？

发表于 2022-01-15 00:53:55 回复(0)

Sqlite

李玉婷的旁听生

not in 后面只能放1列，第一次写的不对

WHERE salary  NOT IN (SELECT MAX(salary) ，MIN(salary) FROM salaries WHERE to_date='9999-01-01')

正确表达

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'

发表于 2021-10-02 21:26:56 回复(0)

Sqlite

牛客523212522号

我认为这道题可以这样考虑，考虑到最大工资和最小工资不止一个，代码可以这么写

select (avg(s2.salary)*count(s2.salary) -max(s2.salary)-min(s2.salary))/(count(s2.salary)-2)
from (select * from salaries where to_date="9999-01-01" ) as s2

发表于 2021-08-04 15:34:15 回复(0)