首发于公众号:小肖学数据分析
描述
有一个员工职称表titles简况如下:
emp_no | title | from_date | to_date |
---|---|---|---|
10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
10003 | Senior Engineer | 2001-12-01 | 9999-01-01 |
10004 | Senior Engineer | 1995-12-01 | 9999-01-01 |
10006 | Senior Engineer | 2001-08-02 | 9999-01-01 |
10007 | Senior Staff | 1996-02-11 | 9999-01-01 |
有一个薪水表salaries简况如下:
emp_no | salary | from_date | to_date |
---|---|---|---|
10001 | 88958 | 1986-06-26 | 9999-01-01 |
10003 | 43311 | 2001-12-01 | 9999-01-01 |
10004 | 74057 | 1995-12-01 | 9999-01-01 |
10006 | 43311 | 2001-08-02 | 9999-01-01 |
10007 | 88070 | 2002-02-07 | 9999-01-01 |
请你统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg,并且以avg升序排序,以上例子输出如下:
title | avg(s.salary) |
---|---|
Senior Engineer | 62409.2500 |
Senior Staff | 88070.0000 |
解题思路:
1、统计各个title类型对应的员工薪水
SELECT
t.title,
s.salary
FROM
titles AS t
INNER JOIN salaries AS s
ON t.emp_no = s.emp_no
2、薪水求平均值,利用avg函数
SELECT
t.title,
avg( s.salary ) AS avg_salary
FROM
titles AS t
INNER JOIN salaries AS s
ON t.emp_no = s.emp_no
GROUP BY
t.title
3、平均薪水升序排列 order by 平均薪水 asc
汇总语句如下
SELECT
t.title,
avg( s.salary ) AS avg_salary
FROM
titles AS t
INNER JOIN salaries AS s
ON t.emp_no = s.emp_no
GROUP BY
t.title
ORDER BY
avg_salary
--测试用例
drop table if exists `salaries` ;
drop table if exists titles;
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 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,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,'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');
PDF版本答案
应读者的要求,我将参考答案已转换成PDF,有需要的同学可以在下方公众号后台回复关键字:加油,即可获取。PDF版本每日更新