首页 > 试题广场 >

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

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

热度指数：351667 时间限制：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

算法知识视频讲解

微众银行内推

这题的坑在“当前”哈哈哈

发表于 2019-05-30 16:56:16 回复(0)

更多回答

山上的小松鼠

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

发表于 2021-07-22 16:32:18 回复(0)

BWTNjIrMFoan

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

主要还是考察group by 和avg() 聚合函数的使用.

order by 中也可以使用聚合函数

发表于 2021-03-20 21:59:31 回复(0)

梦龙吃梦龙

1、关键词：每个title的平均值、当前title类型、当前薪水

2、语句

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

本题主要考查聚合函数avg

发表于 2020-08-02 15:18:03 回复(0)

Jack_Wang

链接：https://www.nowcoder.com/questionTerminal/c8652e9e5a354b879e2a244200f1eaae
来源：牛客网
SELECT t.title, avg(s.salary)
FROM titles t INNER JOIN salaries s
ON t.emp_no = s.emp_no
WHERE t.to_date ='9999-01-01' AND s.to_date = '9999-01-01'
GROUP BY t.title;

发表于 2020-07-12 07:20:20 回复(0)

慕宗悫之长风

CREATE TABLE IF NOT EXISTS "titles" (

题干是错的

发表于 2020-04-04 13:36:49 回复(0)

Sniffer_La_Rose

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

编辑于 2020-02-13 15:50:51 回复(0)

牛客9527为您服务

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

发表于 2019-07-21 15:53:32 回复(0)

Grandenuage

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

发表于 2019-06-26 23:54:58 回复(0)

offer来了请查收

SELECT t.title, AVG(s.salary) AS avg FROM titles AS t
JOIN salaries AS s
ON t.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01' AND t.to_date = '9999-01-01'
GROUP BY t.title;

发表于 2019-06-18 13:32:56 回复(0)

我要扛offer回家

SELECT emp_no,salary FROM salaries
WHERE to_date='9999-01-01'
GROUP BY salary
ORDER BY salary DESC LIMIT 1,1

发表于 2019-04-01 20:31:21 回复(0)

小兵II号

select t.title as title,avg(s.salary) as avg
from titles as t inner join (select * from salaries group by emp_no having to_date = max(to_date)) as s
on s.emp_no = t.emp_no and s.to_date = t.to_date and s.to_date='9999-01-01'
group by t.title;

发表于 2018-04-11 16:46:20 回复(0)

深夜的小虫

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

发表于 2017-11-29 17:55:42 回复(0)

未己呃

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

发表于 2017-07-11 11:18:48 回复(0)

Wahaha同学

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

    and s.to_date = '9999-01-01'  

    and t.to_date = '9999-01-01'  

    group by title  

  and s.to_date = '9999-01-01' 

  and t.to_date = '9999-01-01' 

  这是什么意思啊

发表于 2017-07-09 10:14:13 回复(3)

仰望星空73

select t.title,ROUND(avg(s.salary),1) as avg from

titles as t

inner join

salaries s on t.emp_no = s.emp_no

where s.to_date = '9999-01-01' and t.to_date = '9999-01-01'

group by t.title

首先查询 titles 表，按照 title 进行分组，然后通过emp_no列 inner join salaries ，添加where条件，

查出的平均薪水取一位小数

发表于 2019-03-14 13:45:31 回复(1)

offerrr。。。

select title,avg(salary) 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

发表于 2017-07-14 09:17:59 回复(3)

牛客106702533号

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

发表于 2021-05-06 19:59:19 回复(0)

SU坡馒

好几个题都得写to_date 为什么

select t.title,avg(s.salary)

from salaries s,titles t

where t.emp_no=s.emp_no

and t.to_date='9999-01-01'

and s.to_date='9999-01-01'

group by title;

发表于 2017-07-20 16:34:33 回复(3)

致我们逝去的青春。

SELECT t.title,avg(s.salary)
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 title

发表于 2017-09-24 16:12:48 回复(37)

Coderxxx报道

“Where” 是一个约束声明，使用Where来约束来之数据库的数据，Where是在结果返回之前起作用的，且Where中不能使用聚合函数。

“Having”是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。

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

select title,avg(salary) as avg from salaries,titles where salaries.to_date='9999-01-01'
and titles.to_date='9999-01-01'
group by title having salaries.emp_no = titles.emp_no ;不正确,因为group by之后的表中只有 title，avg字段可以继续进行having处理。我是这样理解的，可能有不足之处。

发表于 2018-01-02 16:25:44 回复(17)