系列文章目录
文章目录
前言
前些天发现了一个巨牛的人工智能学习网站,通俗易懂,风趣幽默,忍不住分享一下给大家。点击跳转到网站,这篇文章男女通用,看懂了就去分享给你的码吧。
描述
查找排除在职(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’);
输出格式:
使用聚合函数,不用子查询
COUNT(1) 代表所有数据长度, -2 代表减去最大最小值的两个长度
SELECT (SUM(salary) - MAX(salary) - MIN(salary)) / (COUNT(1)-2) avg_salary
FROM salaries where to_date = '9999-01-01';
标签:INSERT,01,INTO,salaries,VALUES,SQL,date,平均工资
From: https://blog.csdn.net/pleaseprintf/article/details/140471654