「牛客网SQL实战二刷」是个系列学习笔记博文,每天解析6道SQL题目~ 今天是第7-12 题!该系列的其他博文,可在「我的博客」 中查看~
每篇笔记的格式大致为,三大板块:
- 大纲
- 题目(题目描述、思路、代码、相关参考资料/答疑)**
- 回顾
「往期回顾」❤️
《牛客网SQL实战二刷 | 完整解析 – 目录索引》
上今天的大纲!
一、大纲
题号 | 知识点 |
---|---|
7 | COUNT()函数、GROUP BY 、HAVING子句 |
8 | DISTINCT(GROUP BY去重的用法)、ORDER BY |
9 | INNER JOIN/ 并列查询 |
10 | LEFT JOIN、NOT IN、 IS NULL |
11 | “不等于”的用法 |
12 | MAX()、GROUP BY |
二、题目
7. 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
- 题目描述
查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
CREATE TABLEsalaries
(
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 | t |
---|---|
10001 | 17 |
10004 | 16 |
10009 | 18 |
- 思路
- 「涨幅超过15次」,用COUNT()函数计数,搭配GROUP BY使用,因为是按照对每个员工进行分组后,组内统计;
- 根据输出描述,对COUNT()计算的结果,命名为t;
- 在gropuby子句中使用having,限制t>15,返回满足条件的emp_no。
- 代码
SELECT emp_no, COUNT(emp_no) AS t
FROM salaries
GROUP BY emp_no
HAVING t > 15
- 答疑 - HAVING VS WHERE
在一个sql语句中可以有where子句和having子句。having与where子句类似,均用于设置限定条件;
1)where子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,条件中不能包含聚组函数,使用where条件显示特定的行;
2)having子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚组函数。
来源:https://www.cnblogs.com/cuihongyu3503319/p/9322457.html
8. 找出所有员工当前薪水salary情况
- 题目
找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
CREATE TABLEsalaries
(
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
))
- 输出描述
salary |
---|
94692 |
94409 |
88958 |
88070 |
74057 |
72527 |
59755 |
43311 |
25828 |
- 思路
- 「对于相同的薪水只显示一次」,用DISTINCT 去重;
- 「按照逆序显示」,用ORDER BY 排序。
- 代码
SELECT DISTINCT salary FROM salaries
WHERE to_date = '9999-01-01'
ORDER BY salary DESC
- 其他思路与解法 - GROUP BY 代替 DISTINCT
’ Superyouyo ’ 指出,大数据量时,distinct的效率不高,可以用group by解决重复问题。这让我眼前一亮✨啊!
SELECT salary FROM salaries
WHERE to_date = '9999-01-01'
GROUP BY salary
ORDER BY salary DESC
来源:https://www.nowcoder.com/questionTerminal/ae51e6d057c94f6d891735a48d1c2397
9. 获取所有部门当前manager的当前薪水情况
- 题目描述
获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary,当前表示to_date=‘9999-01-01’
CREATE TABLEdept_manager
(
dept_no
char(4) NOT NULL,
emp_no
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_no
));
CREATE TABLEsalaries
(
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
));
- 输出描述
dept_no | emp_no | salary |
---|---|---|
d001 | 10002 | 72527 |
d004 | 10004 | 74057 |
d003 | 10005 | 94692 |
d002 | 10006 | 43311 |
d006 | 10010 | 94409 |
- 思路
- INNER JOIN 连接两张表,或用并列查询;
- 两个「当前」——当前manager、当前薪水。
- 代码
- 方法一:INNER JOIN
SELECT dm.dept_no, dm.emp_no, s.salary
FROM dept_manager AS dm INNER JOIN salaries AS s
ON dm.emp_no = s.emp_no AND dm.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
- 方法二:并列查询
SELECT dm.dept_no, dm.emp_no, s.salary
FROM dept_manager AS dm, salaries AS s
WHERE dm.emp_no = s.emp_no AND dm.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
- 参考
本题思路同T6,可跳转**《牛客网SQL实战二刷 | Day1》**查看。
来源:https://blog.csdn.net/weixin_44915703/article/details/91348696
10. 获取所有非manager的员工emp_no
- 题目描述
获取所有非manager的员工emp_no
CREATE TABLEdept_manager
(
dept_no
char(4) NOT NULL,
emp_no
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_no
));
CREATE TABLEemployees
(
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 |
---|
10001 |
10003 |
10007 |
10008 |
10009 |
10011 |
- 思路
- 思路一:employees 表 LEFT JOIN dept_emp 表,通过判断 dept_emp对应为空的记录,可以筛选出 「非manager的员工」;
- 思路二:在表employees中排除dept_emp表中的记录,用NOT IN字段。
- 代码
- 方法一:IS NULL
SELECT e.emp_no
FROM employees AS e LEFT JOIN dept_manager AS dm ON e.emp_no = dm.emp_no
WHERE dm.dept_no IS NULL
注意⚠️,是 IS NULL, 不能用 = NULL,详见参考。
- 方法二: NOT IN
SELECT emp_no FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
- 参考
-
如果对LEFT JOIN掌握不足,可以参考题5,可跳转**《牛客网SQL实战二刷 | Day1》**查看,这道题是在T5的基础上,又复杂一些,筛选出为NULL的记录。
来源:https://blog.csdn.net/weixin_44915703/article/details/91348696) -
关于几种JOINS
-
关于 IS NULL 和 = NULL
1)“ = NULL ” 是错误的语法!
2) SQL中,使用 is NULL 或 is not NULL判断字段是否为空
来源:https://blog.csdn.net/qq_16234613/article/details/65627166
11. 获取所有员工当前的manager
- 题目
获取所有员工当前的manager,如果当前的manager是自己的话结果不显示,当前表示to_date=‘9999-01-01’。
结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。
CREATE TABLEdept_emp
(
emp_no
int(11) NOT NULL,
dept_no
char(4) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_no
));
CREATE TABLEdept_manager
(
dept_no
char(4) NOT NULL,
emp_no
int(11) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_no
));
- 输出描述
emp_no | manager_no |
---|---|
10001 | 10002 |
10003 | 10004 |
10009 | 10010 |
- 思路
- INNER JOIN 通过关键字 dept_no连接两张表;
- 「如果当前的manager是自己的话结果不显示」,即dept_manager表的emp_no 与 dept_emp表的emp_no相同的记录,应去掉。
- 代码
SELECT de.emp_no, dm.emp_no AS manager_no
FROM dept_emp AS de INNER JOIN dept_manager AS dm ON dm.dept_no = de.dept_no
WHERE dm.to_date = '9999-01-01' AND de.to_date = '9999-01-01' AND de.emp_no <> dm.emp_no
- 参考 : “不等于”的用法 != VS <>
所有数据库都支持" <> “表示“不等于”, 大部分数据库支持” != “;建议使用” <> "。
来源:https://www.cnblogs.com/wushuo-1992/p/8364828.html
12. 获取所有部门中当前员工薪水最高的相关信息
- 题目
获取所有部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary
CREATE TABLEdept_emp
(
emp_no
int(11) NOT NULL,
dept_no
char(4) NOT NULL,
from_date
date NOT NULL,
to_date
date NOT NULL,
PRIMARY KEY (emp_no
,dept_no
));
CREATE TABLEsalaries
(
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
));
- 输出描述
dept_no | emp_no | salary |
---|---|---|
d001 | 10001 | 88958 |
d002 | 10006 | 43311 |
d003 | 10005 | 94692 |
d004 | 10004 | 74057 |
d005 | 10007 | 88070 |
d006 | 10009 | 95409 |
- 思路
- 「薪水最高」,即MAX(salary);
- 根据输出描述,可见需按照dept_no 分组,即group by dept_no;
- 两张表通过INNER JOIN 连接。
- 代码
SELECT de.dept_no, de.emp_no, MAX(salary)
FROM dept_emp AS de INNER JOIN salaries AS s ON de.emp_no = s.emp_no
WHERE de.to_date = '9999-01-01' and s.to_date = '9999-01-01'
GROUP BY de.dept_no
三、回顾
总结Day2的知识点~
知识点 | 题号 |
---|---|
HAVING子句 | 7 |
去重 | 8 |
“为空” | 10 |
表连接 | 9、10 |
“不等于”/ “不在某子集” | 10、11 |
GROUP BY | 7、8、12 |
函数 | 7「COUNT()」、12「MAX()」 |
除此之外,特别的收获,GROUP BY作为去重的新功能!