统计出当前各个title类型对应的员工当前薪水对应的平均工资【SQL练习题】

描述

有一个员工职称表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

输入：

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

输出：

Senior Engineer|62409.2500
Senior Staff|88070.0000

答案：

WITH
    salaries_join_titles AS (
        SELECT
            salaries.salary,
            titles.title
        FROM
            salaries
            JOIN titles ON salaries.emp_no = titles.emp_no
    )
SELECT
    title,
    avg(salary)
FROM
    salaries_join_titles
GROUP BY
    title
order by avg(salary) asc;

• 数据关联：使用 JOIN 语句将 salaries 和 titles 表通过 emp_no 关联，确保每个员工的职称与其薪水对应

• 数据汇总：使用 GROUP BY 根据 title 分组，并利用 AVG() 函数计算每个职称的平均薪资

• 结果排序：使用 ORDER BY avg(salary) ASC 按照平均薪资从低到高排序