首页 > 试题广场 >

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

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

热度指数：365442 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 32M，其他语言64M
算法知识视频讲解

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

算法知识视频讲解

Sqlite

上善若水201910150811498

select title,avg((select salary from salaries where emp_no=t.emp_no ) ) as salary from titles as t GROUP BY title ORDER BY salary ASC

发表于 2022-08-21 10:26:24 回复(0)

Sqlite

Stzolo

select a.title, avg(b.salary)
from titles a 
join salaries b 
on a.emp_no = b.emp_no
group by title 
order by  avg(b.salary)

发表于 2022-04-21 15:27:47 回复(0)

Sqlite

惊天的泥巴

分组，排序，表连接

select title , avg(s.salary) 
from titles t join salaries s on t.emp_no = s.emp_no
group by title
order by avg(s.salary) asc

发表于 2022-02-27 17:22:45 回复(0)

Sqlite

牛客81376852号

select title,avg(salary) as average
from titles
join salaries
using(emp_no)
group by title
order by average;

发表于 2022-02-12 19:48:44 回复(0)

Sqlite

阴天💙

select t.title, avg(s.salary)

from titles as t, salaries as s

where t.emp_no=s.emp_no

group by t.title

order by avg(s.salary)

发表于 2022-01-20 12:56:05 回复(0)

Sqlite

牛客王小波

select t.title, avg(s.salary) as aslary
from titles as t inner join salaries as s on t.emp_no=s.emp_no
group by t.title having aslary
order by aslary

发表于 2021-12-02 13:50:48 回复(0)

Sqlite

牛客303661432号

select t.title,avg(s.salary) as avg from salaries s ,titles t where s.emp_no = t.emp_no and s.to_date='9999-01-01'
and t.to_date='9999-01-01'
group by title

order by avg(s.salary)

知识点：avg() 、group by

发表于 2021-11-22 16:16:53 回复(0)

Sqlite

LY_coder

*avg、内连接、group by、order by

select t.title,avg(s.salary) as avg_s

from titles t,salaries s
where t.emp_no = s.emp_no
group by t.title
order by avg_s

发表于 2021-11-17 10:49:11 回复(0)

Sqlite

jsc西内

select a.title,avg(s.salary) from titles a
join salaries s on a.emp_no = s.emp_no
group by title
order by avg(s.salary)

发表于 2021-10-05 22:26:18 回复(0)

Sqlite

IPersisttfIa.

SELECT t.title AS title, AVG(s.salary) FROM titles t JOIN salaries s ON t.emp_no = s.emp_no GROUP BY(t.title) ORDER BY AVG(s.salary)

Coderxxx报道提到了where过滤与 having过滤的区别：

1 在功能上，Where 子句能做的，Having 子句基本上都能做。唯一的差别是 Where 子句用来过滤行，而 Having 子句用于过滤分组。当存在分组操作时，Where 子句在数据分组前过滤，Having 子句在数据分组后进行过滤。

2 在性能方面，对于索引字段，放在 Where 子句中能有索引，而放在 Having 子句中不能走索引。

3 having可以和count、sum、avg、max、min等聚合函数一起使用，而where则不能，否则会报错。

发表于 2021-09-28 08:56:49 回复(0)

Sqlite

勇敢丫丫不怕困难

select t.title,avg(s.salary)
from titles t,salaries s
where t.emp_no=s.emp_no
group by t.title
order by avg(s.salary)

发表于 2021-09-23 10:28:50 回复(0)

Sqlite

没有鱼丸没有粗面

select title,avg(salary) as salary_avg
from(
    select t1.emp_no
        ,t1.title
        ,t2.salary
    from titles t1
    left join salaries t2
    on t1.emp_no = t2.emp_no
        and t1.to_date = '9999-01-01'
        and t2.to_date = '9999-01-01'
    )t
group by title
order by salary_avg asc

发表于 2021-09-17 11:25:26 回复(0)

Sqlite

qxt27

select a.title,avg(s.salary)
from titles a
left join salaries s
on a.emp_no=s.emp_no
group by a.title
order by avg(s.salary);

发表于 2021-09-10 14:38:37 回复(0)

Sqlite

CeciliaWong

select distinct title, avg(salary) OVER(PARTITION BY title) as average_salary
from(
select t.emp_no,t.title,s.salary
from titles t
join salaries s
where t.emp_no = s.emp_no) a
order by 2

发表于 2021-09-03 09:03:50 回复(0)

Sqlite

想学编程的樱木

select t.title,avg(s.salary) from
titles t join salaries s
on t.emp_no = s.emp_no
group by title
order by avg(s.salary);

发表于 2021-08-17 17:16:21 回复(0)

Sqlite

牛客112419884号

select * from (
select aa.title,(select avg(b.salary)
from titles a
inner join salaries b on a.emp_no=b.emp_no
where a.title=aa.title
group by a.title) as cc
from titles aa
group by aa.title
) ccc
order by ccc.cc asc

发表于 2021-08-12 09:25:57 回复(0)