首页 > 试题广场 >

平均工资

[编程题]平均工资

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

算法知识视频讲解

Mysql

饿不死就行了

# select    # 窗口函数
#     avg(salary)
# from
#     (select 
#         max(salary) over() as ma,
#         min(salary) over() as mi,
#         salary
#     from 
#         salaries           as sa
#     where 
#         to_date ='9999-01-01')
# where 
#     salary <> ma and salary <> mi

# 直接计算去掉最大最小
SELECT
(SUM(salary) - MAX(salary) - MIN(salary)) / (COUNT(*) - 2)
FROM salaries
WHERE to_date = '9999-01-01'

发表于 2025-06-17 18:01:41 回复(0)

Mysql

燃烧的雪花

select 
avg(salary) avg_salary
from
salaries
where
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')
and to_date='9999-01-01'

发表于 2025-02-07 15:37:03 回复(0)

Mysql

刷牛客的coder很爱吃香菜

select
    avg(salary)
from
    salaries
where
    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'
    )
    and to_date = '9999-01-01'

发表于 2025-01-07 10:05:55 回复(0)

Mysql

青衣白马少年时

select avg(salary) avg_salary
from salaries
join (select to_date,max(salary) max, min(salary) min from salaries
    where to_date = '9999-01-01'
) ex using(to_date)
where salary != max and salary != min

y一种思路，先找到当前最高和最低工资，然后根据to_date与原表内连接，然后计算平均，不过这种方法可能不适合较大数据集

发表于 2024-12-31 15:24:20 回复(0)

Mysql

牛客803778361号

为什么WHERE里套用子查询别名会报错啊

SELECT AVG(subsq.salary) AS avg_salary
FROM (SELECT salary
      FROM salaries
      WHERE to_date = '9999-01-01') AS subsq
WHERE   subsq.salaries != (SELECT (MAX(subsq.salary) FROM subsq))
    AND subsq.salaries != (SELECT (MIN(subsq.salary) FROM subsq));

发表于 2024-09-15 13:38:16 回复(0)

Mysql

-暗号-

with t1 as (
    select *,lead(salary) over(order by salary) as 'lead_salary',
    lag(salary) over(order by salary) as 'lag_salary'
    from salaries where to_date='9999-01-01' 
) # 求工资排序后的前一个值和后一个值
select avg(salary) as 'avg_salary' from t1 where lead_salary is not null and lag_salary is not null

发表于 2024-08-16 14:09:15 回复(0)

Mysql

有点心碎的小龙虾很靠谱

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

发表于 2024-07-03 16:47:44 回复(1)

Mysql

SummerSue

select
    avg(salary)
from salaries
where 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'
)
and to_date = '9999-01-01'

发表于 2024-06-05 01:00:09 回复(0)

Mysql

玎瑱

select avg(a.salary) as avg_salary

from

(select *,rank() over(order by salary desc) rankmax,rank() over(order by salary) rankmin

from salaries

where to_date = '9999-01-01') as a

where a.rankmax>1 and a.rankmin>1

发表于 2024-05-21 11:04:11 回复(0)

Mysql

FMW

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

发表于 2024-04-02 13:12:15 回复(3)

Mysql

牛客884909151号

就小于最大的,大于最小的,between也行

发表于 2024-03-26 14:17:12 回复(0)

Mysql

菜鸡真的不配有offer

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'
union
select min(salary)
from salaries 
where to_date = '9999-01-01'
)

发表于 2024-03-17 13:24:28 回复(0)

Mysql

小谷围做题家

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

发表于 2024-02-07 13:56:28 回复(0)

Mysql

暴徒哥白尼

#使用子查询和窗口排序解题
select avg(salary) avg_salary 
from (select salary,rank()over(order by salary) rk,rank()over(order by salary desc) rkd
from salaries
where to_date ='9999-01-01') af
where rk<>1 and rkd<>1

发表于 2024-01-23 16:31:22 回复(0)

Mysql

牛客605111887号

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

发表于 2024-01-14 16:28:22 回复(0)

Mysql

TATA_x

select avg(salary) from salaries

where salary not in ((select min(salary) from salaries

where to_date='9999-01-01'),(select max(salary) from salaries where to_date='9999-01-01'))

and to_date='9999-01-01';

发表于 2023-11-18 17:08:15 回复(0)

Mysql

dolen

# 查找排除在职(to_date = '9999-01-01' )员工的最大、最小salary之后，其他的在职员工的平均工资avg_salary。

with dwd_data as (
select 
    emp_no,
    salary,
    max(salary) over() as max_salary,
    min(salary) over() as min_salary
from salaries
where to_date = '9999-01-01'
)

select avg(salary) from dwd_data 
where salary not in (max_salary,min_salary);

发表于 2023-09-12 16:50:07 回复(0)

Mysql

麻辣小小龙虾

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

发表于 2023-08-17 20:55:10 回复(0)