牛客网-数据库SQL实战16

最新推荐文章于 2023-01-05 08:58:26 发布

原创最新推荐文章于 2023-01-05 08:58:26 发布 · 163 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #数据库

数据库专栏收录该内容

25 篇文章

订阅专栏

本文介绍如何通过SQL查询统计当前各个职位类型的平均薪资水平。利用salaries和titles表中的数据，筛选出有效的薪资记录，并按职位类型分组计算平均薪资。

统计出当前各个title类型对应的员工当前薪水对应的平均工资

题目描述
解题思路
参考代码

题目描述

统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。

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`));
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

如插入：

INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'1986-12-01','1995-12-01');
INSERT INTO salaries VALUES(10004,74057,'1995-12-01','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');

INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','2001-12-01','9999-01-01');
INSERT INTO titles VALUES(10004,'Engineer','1986-12-01','1995-12-01');
INSERT INTO titles VALUES(10004,'Senior Engineer','1995-12-01','9999-01-01');
INSERT INTO titles VALUES(10006,'Senior Engineer','2001-08-02','9999-01-01');
INSERT INTO titles VALUES(10007,'Senior Staff','1996-02-11','9999-01-01');

输出：
在这里插入图片描述

解题思路

1.根据题目的要求筛选出titles表和salaries表中满足to_date='9999-01-01’的表项
2.用GROUP BY子句将筛选的表项按照title进行分组

参考代码

SELECT T.title , AVG(S.salary) AS avg
FROM salaries AS S INNER JOIN titles AS T
  ON S.emp_no = T.emp_no
 AND S.to_date='9999-01-01'
 AND T.to_date='9999-01-01'
GROUP BY T.title