牛客在线编程练习：SQL16_中等

最新推荐文章于 2024-04-22 17:01:12 发布

我叫吉姆

最新推荐文章于 2024-04-22 17:01:12 发布

阅读量98

点赞数

分类专栏：牛客在线编程_SQL篇文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_42623730/article/details/113500270

版权

牛客在线编程_SQL篇专栏收录该内容

62 篇文章 0 订阅

订阅专栏

SQL练习：SQL16_中等

题目：
统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。

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 IF NOT EXISTS "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,70698,'1986-12-01','1995-12-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,'Engineer','1986-12-01','1995-12-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');

在这里插入图片描述

题解

#方式1：隐式内连接
SELECT title,AVG(salary)
FROM salaries S,titles T
WHERE S.emp_no = T.emp_no AND T.to_date='9999-01-01' AND S.to_date='9999-01-01'
GROUP BY T.title

#方式2：显式内连接
SELECT title,AVG(salary)
FROM salaries S
INNER JOIN titles T ON S.emp_no = T.emp_no
WHERE T.to_date='9999-01-01' AND S.to_date='9999-01-01'
GROUP BY T.title

#方式3：左外连接
SELECT title,AVG(salary)
FROM salaries S
LEFT OUTER JOIN titles T ON S.emp_no = T.emp_no
WHERE T.to_date='9999-01-01' AND S.to_date='9999-01-01'
GROUP BY T.title

#方式4：右外连接
SELECT title,AVG(salary)
FROM salaries S
RIGHT OUTER JOIN titles T ON S.emp_no = T.emp_no
WHERE T.to_date='9999-01-01' AND S.to_date='9999-01-01'
GROUP BY T.title

## 1.【avg】函数修饰的字段默认命名为avg，不用重命名。