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