首页 > 试题广场 >

平均工资

[编程题]平均工资

热度指数：178866 时间限制：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

算法知识视频讲解

ciphersaw

本题逻辑有问题，在挑选当前最大、最小salary时没加 to_date = '9999-01-01' 作条件限制，导致挑选出来的是全表最大、最小salary，然后对除去这两个salary再作条件限制 to_date = '9999-01-01' ，求平均薪水，此时求出的平均薪水与题目逻辑要求的不同。

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

正确的逻辑应如下所示，但在本题OJ系统中通不过：

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

编辑于 2017-07-24 09:42:33 回复(58)

yeewingho

错误写法：

select avg(salary) as avg_salary

from salaries
where salary>min(salary) and salary<max(salary)

and to_date='9999-01-01'

错误原因：where 后面不能跟分组函数，因为执行顺序是先执行where 之后才执行分组，还没有分组的时候是无法运行min,max,avg，sum,count这类函数的！

https://blog.csdn.net/liangjiabao5555/article/details/105474876

解决方法：将where后面的分组函数改成子查询！

正确解法：

select avg(salary) as avg_salary
from salaries
where salary>(select min(salary) from salaries) and salary<(SELECT max(salary) from salaries)
and to_date='9999-01-01'

发表于 2020-05-14 16:36:26 回复(11)

牛客918800268号

麻瓜数学版

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

发表于 2021-01-19 16:59:32 回复(10)

Mysql

简雨Jane

错误写法：where 语句后面不能操作表，select子查询查询的是子表
 select 
 avg(salary) 
 from
 salaries
 where to_date = '9999-01-01'
 and salary not IN
 (select max(salary),min(salary) from salaries where to_date = '9999-01-01')

正确写法：分开来写

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

法2：窗口函数计算

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

发表于 2021-11-06 11:04:44 回复(4)

ywcai

通过连接表的方式来处理

select avg(s.salary) from salaries s

inner join (select max(salary) as mx from salaries ) maxs
inner join (select min(salary) as mi from salaries ) mins
where
s.salary<mx
and
s.salary>mi
and
s.to_date='9999-01-01'

发表于 2018-10-03 18:15:54 回复(1)

wn箬笠蓑衣

select (sum(salary)-max(salary)-min(salary))/(length(salary)-2) as avg_salary from salaries
where to_date='9999-01-01'

这样写为什么通过不了啊

发表于 2018-05-06 21:21:37 回复(13)

Mysql

牛客516632226号

不能同时写（select min(salary),max(salary) )

这是两列而不是两行用in的话必须是一行或多行

发表于 2022-08-18 11:52:11 回复(1)

hhchhhch

  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') 
  
 
  两个括号里面应该也有日期限制吧   

发表于 2017-08-17 01:29:00 回复(6)

平安是福花开富贵

# 法一：窗口函数
select avg(salary)
from 
(
    select 
    emp_no,
    salary,
    rank() over(order by salary) as rk1,
    rank() over(order by salary desc) as rk2
from salaries
where to_date = '9999-01-01'
) a
where rk1 != 1 and rk2 != 1

#法二：not in
select avg(salary) as avg_salary from salaries where salary
not in(
    (select max(salary) from salaries where to_date='9999-01-01'),
    (select min(salary) from salaries where to_date='9999-01-01')
)
and to_date='9999-01-01'

注意：不能同时写（select min(salary),max(salary) ):这是两列而不是两行用in的话必须是一行或多行

发表于 2022-10-10 22:47:04 回复(0)

1988coding

select avg(salary) as avg_salary from salaries 
where 
salary not in (select min(salary )from salaries  ) and
salary not in  (select max(salary )from salaries  ) and
to_date='9999-01-01'

min()和max()都是聚合函数，是对结果集中的列进行操作而不是对单个记录进行操作

发表于 2017-08-31 12:01:14 回复(2)

牛客626904108号

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

发表于 2020-09-10 12:17:13 回复(0)

燕北茶

with z as (select * from salaries where to_date= '9999-01-01')
select avg(salary) from z
where salary > (select min(salary) from z) and salary< (select max(salary) from z)

发表于 2022-05-15 21:45:52 回复(0)

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)

Mysql

Ron1997

with s as(
select
    max(salary) as maxx,
    min(salary) as minn
from salaries
where to_date="9999-01-01"
)

select avg(salary)
from salaries join s
where to_date="9999-01-01"
and salaries.salary !=s.minn and salaries.salary !=s.maxx

发表于 2022-03-23 20:17:22 回复(1)

Mysql

阿媛7856

select avg(salary) as avg_salary from salaries where salary
not in(
    (select max(salary) from salaries where to_date='9999-01-01'),
    (select min(salary) from salaries where to_date='9999-01-01')
)
and to_date='9999-01-01'

发表于 2021-10-08 14:24:22 回复(0)

Mysql

van_f

由于可能存在多个最大salary，只用salary去做分组出来的结果不对。
所以考虑用员工号emp_no做限制。
先找到最大（最小）salary的员工号emp_no，然后取编号最大的员工号emp_no，即取出唯一最大值和最小值。这样就避免了只用salary去做限制从而出现舍掉多个值的情况。

SELECT avg(salary) avg_salary FROM salaries
WHERE emp_no NOT IN
(SELECT max(emp_no) FROM salaries
WHERE salary IN (SELECT max(salary) salary FROM salaries WHERE to_date = '9999-01-01'))
AND emp_no NOT IN
(SELECT max(emp_no) FROM salaries
WHERE salary IN (SELECT min(salary) salary FROM salaries WHERE to_date = '9999-01-01'))
AND to_date = '9999-01-01'

发表于 2021-08-13 10:11:02 回复(1)

Mysql

牛客匿名用户202212

select * from salaries where to_date='9999-01-01' t;
#这个语句运行不了
select avg(t.salary) 
from t
where t.salary>(select min(t.salary) from t)
and t.salary<(select max(t.salary) from t)

有谁告诉我一下我这个语句哪里错了吗？思路是先取出所有to_date为‘9999-01-01’的值生成一张表t，

然后在t表中去掉最高值和最低值求平均值

发表于 2021-07-15 13:54:50 回复(1)

晓_

    select
        avg(salary) as avg_salary
    from
        salaries
    where
        to_date='9999-01-01'
    and
        salary > (select min(salary) from salaries where to_date='9999-01-01')
    and
        salary < (select max(salary) from salaries where to_date='9999-01-01')

发表于 2021-07-01 10:21:06 回复(0)

XVenlenter

select avg(sa.salary) as avg_salary
from salaries sa,
(select max(salary) as maxSalary,min(salary) as minSalary from salaries where to_date = '9999-01-01') mm
where sa.salary !=mm.maxSalary and sa.salary!=mm.minSalary and sa.to_date = '9999-01-01';

发表于 2021-03-15 14:24:30 回复(1)

某位同学

解法一：

select avg(salary) as avg_salary
from salaries
where to_date = '9999-01-01' 
and salary <> (select max(salary) from salaries where to_date = '9999-01-01') 
and salary <> (select min(salary) from salaries where to_date = '9999-01-01');

解法二：

select avg(sr.salary) as avg_salary
from (select salary, 
      rank() over(order by salary asc) as salary_asc, 
      rank() over(order by salary desc) as salary_desc
      from salaries
      where to_date = '9999-01-01' ) sr
where sr.salary_asc <> 1 and sr.salary_desc <> 1

编辑于 2020-08-27 22:36:01 回复(0)