牛客网SQL实战二刷 | Day3

「牛客网SQL实战二刷」是个系列学习笔记博文，每天解析6道SQL题目～ 今天是第13-18 题！

每篇笔记的格式大致为，三大板块：

• 大纲
• 题目（题目描述、思路、代码、相关参考资料/答疑）
• 回顾

「往期回顾」❤️
《牛客网SQL实战二刷 | 完整解析 – 目录索引》

一、大纲

题号	知识点
13	GROUP BY, COUNT()
14	GROUP BY, COUNT(), DISTINCT
15	筛选奇数，“不等于”的表达，ORDER BY
16	GROUP BY, AVG()
17	ORDER BY, DISTINCT, LIMIT, OFFSET
18	SELECT嵌套，MAX()

二、题目

13.从titles表获取按照title进行分组

• 题目描述

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
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);

• 输出描述

title	t
Assistant Engineer	2
Engineer	4
省略	省略
Staff	3

• 思路

1. 「按照title分组」，用GROUP BY；
2. 「给出对应数目t」，则在每个分组下，用COUNT（）统计emp_no的数目。

• 代码

SELECT title, COUNT(emp_no) AS t
FROM titles
GROUP BY title
HAVING t >= 2

14. 从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略

• 题目描述

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略。
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);

• 输出描述

title	t
Assistant Engineer	2
Engineer	3
省略	省略
Staff	3

• 思路
  在第13题的基础上改进代码，用DISTINCT对组内emp_no去重

• 代码

SELECT title, COUNT(DISTINCT emp_no) AS t
FROM titles
GROUP BY title
HAVING t >= 2

15. 查找employees表

• 题目描述

查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));

• 输出描述

emp_no	birth_date	first_name	last_name	gender	hire_date
10011	1953-11-07	Mary	Sluis	F	1990-01-22
10005	1955-01-21	Kyoichi	Maliniak	M	1989-09-12
10007	1957-05-23	Tzvetan	Zielinski	F	1989-02-10
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10009	1952-04-19	Sumant	Peac	F	1985-02-18

• 思路

1. 「emp_no为奇数」，即emp_no除以2的余数为1，注意emp_no本身已经是int数值型变量了；
2. 「last_name不为Mary」，即last_name不等于 Mary；
3. ORDER BY DESC 表示逆序。

• 代码

SELECT * FROM employees
WHERE emp_no % 2 == 1 AND last_name <> 'Mary' 
ORDER BY hire_date DESC

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

• 题目描述

统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出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);

• 输出描述

title	avg
Engineer	94409.0
Senior Engineer	69009.2
Senior Staff	91381.0
Staff	72527.0

• 思路

1. 按照title类型统计，因此是用GROUP BY分组；
2. 通过emp_no连接两张表；
3. AVG（）函数计算平均值；
4. 「当前员工」和「当前薪水」通过 to_date限制。

• 代码

SELECT title, AVG(salary) AS avg
FROM salaries AS s, titles AS 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

• 参考资料：WHERE ? HAVING
  “Where” 是一个约束声明，使用Where来约束来之数据库的数据，Where是在结果返回之前起作用的，且Where中不能使用聚合函数。
  “Having”是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。
  （来源：https://www.nowcoder.com/questionTerminal/c8652e9e5a354b879e2a244200f1eaae）

17. 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

• 题目描述

获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary
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));

• 输出描述

emp_no	salary
10009	94409

• 思路

1. 只取出「薪水第二多」这一条记录，参考第2题（2. 查找入职员工时间排名倒数第三的员工所有信息），ORDER BY排序后，用 LIMIT 和 OFFSET限制；
2. 代码非常严谨的用了 DISTINCT，考虑了salary有重复的情况。

• 代码

SELECT emp_no, salary
FROM salaries
WHERE to_date = '9999-01-01' AND salary = 
    (SELECT DISTINCT salary FROM salaries WHERE to_date = '9999-01-01'
    ORDER BY salary DESC LIMIT 1 OFFSET 1)

• 参考资料：LIMIT 和 OFFSET的用法
  举个例子，LIMIT 3 OFFSET 1， 这意味着，跳过第1条记录（即从第2条记录开始），返回接下来3条记录。即最终得到，原本的第2，3，4条记录。

（来源：https://www.runoob.com/sqlite/sqlite-limit-clause.html）

18. 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary，不准使用order by

• 题目描述

查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
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));

• 输出描述

emp_no	salary	last_name	first_name
10009	94409	Peac	Sumant

• 思路

1. 本题同第17题，但限制是用ORDER BY进行排序，因此难点在于如何筛选出第二大的salary；
2. 思路在于，先找出所有中最大的salary即为第一大的salary，排除该记录后，剩下中的最大的salary即为第二大的salary了。因此，结合使用MAX（）函数和SELECT嵌套。

• 代码

SELECT e.emp_no, MAX(salary), e.last_name, e.first_name
FROM employees AS e INNER JOIN salaries AS s ON e.emp_no = s.emp_no 
WHERE s.to_date = '9999-01-01' AND s.salary NOT IN
    (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01' )

三、回顾

按照知识点，总结归纳题目。

知识点	题号
判断奇数	15
SELECT嵌套	18
LIMIT， OFFSET	17
ORDER BY	15，17
DISTINCT去重	14，17
GROUP BY	13， 14， 16
函数	13「COUNT（）」, 14「COUNT（）」, 16「AVG（）」, 18「MAX（）」

今日份的知识点还是比较多的哦。