SQL54 平均工资

最新推荐文章于 2023-09-02 18:12:13 发布

敏敏程序媛

最新推荐文章于 2023-09-02 18:12:13 发布

阅读量2.1k

点赞数

分类专栏： sql题库训练文章标签： sqlite 数据库 sql

本文链接：https://blog.youkuaiyun.com/qq_28841515/article/details/123208055

版权

sql题库训练专栏收录该内容

45 篇文章

订阅专栏

该博客讨论了如何在SQLite数据库中查询排除最高和最低薪资后，在职员工的平均工资。通过创建salaries表并插入样本数据，展示了SQL查询语句来实现这一目标。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

描述

查找排除在职(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

-- 题目：查找排除在职员工的最大、最小salary之后，其他的在职员工的平均工资
-- 方法一：采用子查询的方法
/*
select 
    avg(salary) 
from 
    salaries s
where 
    s.salary not in 
    (select 
    max(salary)
from salaries s
where
    s.to_date = '9999-01-01' )
and
     s.salary not in 
    (select 
    min(salary)
from salaries s
where
    s.to_date = '9999-01-01' )
and  s.to_date = '9999-01-01'
 

-- 方法二：使用聚合函数，不适用子查询，使用聚合函数，不用子查询
-- COUNT(1) 代表所有数据长度， -2 代表减去最大最小值的两个长度
-- 这种情况，忽略了多个最大值和多个最小值的情况
select 
    (sum(salary)-max(salary)-min(salary))/(count(1)-2) avg_salary
from
    salaries s
where 
    to_date = '9999-01-01';
    
-- 解法二 窗口函数

select avg(ss.salary)
from 
    (
        select *
            ,row_number() OVER(order by salary) r1
            ,row_number() over(order by salary desc) r2
        from 
            salaries
        where to_date = '9999-01-01'
    ) ss
where ss.r1 <> 1
    and ss.r2 <> 1
    and to_date = '9999-01-01'

*/ 


-- 解法四： 把最大和最小去掉  
    
    
select 
    avg(salary)
from 
    (
        select * 
        ,max(salary) over() s1
        ,min(salary) over() s2
        from
            salaries
        where to_date = "9999-01-01"
    ) ss
 where ss.salary <ss.s1
     and ss.salary >ss.s2