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

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

输入

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

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)

小兵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)

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)

PigXmAa

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) asc

题目说的根据avg升序你们都丢了么？？？

关于to_date = '9999-01-01'在刷题中并不影响如果有明确时间要求加上即可

发表于 2021-03-03 16:06:27 回复(18)

荷塘月色836

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

注意这里avg(salary) AS avg，由实例的结果我们可以知道需要avg，如果不设置别名，列名为：avg(salary)

发表于 2019-10-26 18:42:52 回复(2)

Benjaminz

为什么这里不能用having

select title,avg(salary) as avg

from titles 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

上面是对的，可是下面就错了：

select title,avg(salary) as avg

from titles join salaries

on titles.emp_no=salaries.emp_no

group by title

having titles.to_date='9999-01-01'

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

发表于 2017-08-02 15:56:13 回复(17)

livepool86

SELECT t.title,avg(s.salary)
FROM salaries as s INNER JOIN titles as t
ON s.emp_no = t.emp_no
GROUP BY title;

发表于 2021-03-28 10:08:10 回复(0)

xbcdbc

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

1. 需要返回 2 列，写上 title, avg(salary)

2. 内连接 2 张表，需要加上关联条件是员工号相同 salary.emp_no = ti.emp_no

3. 查询的约束条件是两个日期，加上 WHERE

4. 最后得到的数组是符合条件的数组，对 title 分组，可以求得 avg(salary)

发表于 2020-09-11 14:58:37 回复(0)

兔子的救兵

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

主要用avg()函数和group by 即可

发表于 2017-12-15 10:04:19 回复(1)

马卡维奇

SELECT title , AVG(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 t.title;

发表于 2017-08-05 16:31:05 回复(3)

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

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)

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

输入

输出

问题信息

热门推荐

通过挑战的用户

相关试题