SQL练习（通过练习写出有意思有用的sql） _有意思

大鹏一日同风起，扶摇直上九万里。这篇文章主要讲述SQL练习：通过练习写出有意思有用的sql相关的知识，希望能为你提供帮助。
题目来源：牛客网
解答：老表
51CTO博客地址：https://blog.51cto.com/u_13334898
个人微信公众号：简说python
大家好，我是老表，在这里会记录我的SQL刷题笔记，目前已经更新到了SQL21，欢迎大家进行学习交流、指正，一起加油。
SQL1 查找最晚入职员工的所有信息

文章图片

我的思路：根据hire_date（员工入职日期）进行降序排序，然后排第一位的就是最晚入职的员工。
我的题解：

select * from employees order by hire_date desc limit 1;

涉及知识点：

排序：order by 字段名称 desc/asc （desc 降序，asc 升序）
取出指定列：limit n

提交结果：

文章图片

其他题解学习： 
这个比最开始解法好，如果有多个员工都是最晚入职的，这样也可以筛选出来。
思路：先取出employees表中hire_date的最大值，然后再从employees表中选取出所有hire_date等于最大值的行。

select * from employees where hire_date = ( select max(hire_date) from employees );

另外还有几个新增知识点：

max函数，取出最大值，参数为列名（字段名字）
使用limit与offset关键字，返回n行数据，从第m+1行开始返回：limit n offset m
使用limit关键字从第m+1条记录开始，返回n行数据，也就是第一条记录 limit m,n，如果m=0，就等于limit n

理由很简单，聚合函数是对列进行操作的，而where是对行数据进行筛选的，所以不能在where中不能使用聚合函数，但是在having中是可以使用的。
为什么呢？你可以评论区留言说说你的看法。
SQL2 查找入职员工时间排名倒数第三的员工所有信息

文章图片

我的思路：先取出入倒数第三的hire_date（员工入职日期），然后取出对应hire_date（员工入职日期）的数据行即可。
我的题解：

select * from employees where hire_date = ( select hire_date from employees group by hire_date order by hire_date desc limit 2,1 );

涉及知识点：

子查询（嵌套查询）
子查询sql执行顺序：from-group by-select-order by-limit，补充完整sql执行顺序：
from -> where -> group by -> having -> select -> order by -> limit
排序：order by 字段名称 desc/asc （desc 降序，asc 升序）
从指定位置开始取出指定行：limit m,n

提交结果：

文章图片

其他题解学习： 
思路：首先按hire_date进行降序排序，然后直接利用row_number()函数给数据进行标号，取出标号为3的那条数据即可。

select emp_no, birth_date, first_name, last_name, gender, hire_date from ( select *, row_number() over (order by hire_date desc) as rownum from employees ) a1 where rownum = 3;

另外还有几个新增知识点：

row_number() over (partition by col1 order by col2)表示按col1进行分区，然后每个区块内按col2进行升序排序，并给对应的区块内编号（也可以不写分区，就整个数据集为一个分区）；

理论上来说如果有相同入职时间的人，这种方法就不科学了。
SQL3 查找当前薪水详情以及部门编号dept_no

文章图片

文章图片

我的思路：直接通过emp_no连接两个表就可以啦，这里的技巧就是左表优先选数据量小的表，比如题目中dept_manager就是相对小的表。
我的题解：

select a.emp_no, b.salary, b.from_date, b.to_date, a.dept_no from (select * from dept_manager ) as a join (select * from salaries ) as b on a.emp_no = b.emp_no order by a.emp_no;

涉及知识点：

join 连接，还有左连接(left join)和右连接(right join)
sql 执行顺序（一天发一遍，记住）
from -> where -> group by -> having -> select -> order by -> limit
排序：order by 字段名称 desc/asc （desc 降序，asc 升序）

提交结果：

文章图片

其他题解学习： 
思路：没有什么特别的，主要是将join改为了where实现，另外提出了应该限制时间的想法（应该只统计目前还在职的员工相关信息）。

select b.*,a.dept_no from dept_manager as a, salaries as b where a.to_date=\'9999-01-01\' and b.to_date=\'9999-01-01\' and a.emp_no = b.emp_no order by b.emp_no;

SQL4 查找所有已经分配部门的员工的last_name和first_name以及dept_no

文章图片

文章图片

我的思路：直接通过emp_no连接两个表就可以啦，和上一个题几乎一样。
我的题解：

select a.last_name, a.first_name, b.dept_no from (select emp_no, dept_no from dept_emp) as b join (select emp_no, last_name, first_name from employees) as a on b.emp_no = a.emp_no;

涉及知识点：

子查询（嵌套查询）
完整sql执行顺序（每天看一遍，不信记不住）：
from -> where -> group by -> having -> select -> order by -> limit

提交结果：

文章图片

SQL5 查找所有员工的last_name和first_name以及对应部门编号dept_no

文章图片

我的思路：直接通过emp_no连接两个表就可以啦，和上一个题不同的是题目有说明也包括暂时没有分配具体部门的员工，所以这里使用dept_emp表右链接employees表。
我的题解：

select a.last_name, a.first_name, b.dept_no from (select emp_no, dept_no from dept_emp) as b right join (select emp_no, last_name, first_name from employees) as a on b.emp_no = a.emp_no;

涉及知识点：

子查询（嵌套查询）
完整sql执行顺序（每天看一遍，不信记不住）：
from -> where -> group by -> having -> select -> order by -> limit

提交结果： 

文章图片

SQL7 查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t

文章图片

文章图片

我的思路：在薪水表中根据员工编号分组，然后计算每个组内的数据条数，筛选出数据条数大于15的员工编号和对应的数据条数即可。
我的题解：

select emp_no, count(emp_no) as t from salaries group by emp_no having count(emp_no) > 15;

涉及知识点：

聚合函数count()计数
完整sql执行顺序（每天看一遍，不信记不住）：
from -> where -> group by -> having -> select -> order by -> limit
group by 分组，having配合分组后筛选

提交结果：

文章图片

其他题解学习：

文章图片

在这题的讨论里看到这样一条讨论，然后看了下这个讨论下的评论，大家讨论的点是这题想统计的员工薪资涨幅次数大于15次的数据，而不是薪资表中有15条以上记录的员工数据。
估计这题原本是统计salaries表中员工薪水涨幅次数大于15次的员工编号和涨幅次数，那我们顺着这个思维来思考下吧。
思路：首先涨幅肯定是想统计正涨幅，即按时间相邻两次薪水差为正值，然后统计涨幅超过15次的员工编号
理论上感觉不存在，一般调薪少则一个季度一次，多则一年一次，能调薪15次以上，应该是元老级别了！所以这样一想，可能原本出题者就是想考下大家group by having，统计下薪水发放次数。
当然，既然遇到这种思维，我们就顺着去练一练。
本题其他题解来自讨论区的LeronTalks。

文章图片

select tmp.emp_no, count(tmp.whether) as t from ( select emp_no, ifnull(s1.salary > (select salary from salaries as s2 where s1.from_date > s2.from_date and s1.emp_no = s2.emp_no order by s2.from_date desc limit 1), 1) as whether from salaries as s1) as tmp group by tmp.emp_no having count(tmp.whether) > 15;

另外还有几个新增知识点：

控制流函数ifnull(a,b)，如果a为null或者0，则ifnull的值就为b，否则就为a；
count(null)为0。

这个评论很到位，学习、工作、生活中我们都应该多思考，而不是把自己困在既有的条条框框下，加油！

文章图片

SQL8 找出所有员工当前薪水salary情况

文章图片

我的思路：首先筛选出题目中两个关键信息：只要薪水这一列、需要去重降序排列；按要求，我们 select salary，利用group by进行去重，order by salary desc降序排列，同时我们用where to_date = \'9999-01-01\'筛选出了当前还在公司的员工。
我的题解：

select salary from salaries where to_date = \'9999-01-01\' group by salary order by salary desc;

涉及知识点：

完整sql执行顺序（每天看一遍，不信记不住）：
from -> where -> group by -> having -> select -> order by -> limit
group by单列分组 == 去重
order by 列名 desc降序排序
distinct也可以去重复，但是效率相比group by会低一些（数据量大时）

提交结果：

文章图片

SQL10 获取所有非manager的员工emp_no

文章图片

文章图片

我的思路：在员工表employees中取出员工编号不在部门领导表dept_manager中的员工编号即可。
我的题解：

select emp_no from employees where emp_no not in (select emp_no from dept_manager ) ;

涉及知识点：

完整sql执行顺序（每天看一遍，不信记不住）：
from -> where -> group by -> having -> select -> order by -> limit
where筛选+子查询
not in / in 可以判断一个值在不在一个集合中

提交结果：

文章图片

其他题解：
也是筛选思路，先将dept_manager和employees连接起来，然后筛选出部门编号为空的员工编号即可，利用group by去重。

select b.emp_no from dept_manager as a right join employees as b on a.emp_no = b.emp_no where a.dept_no is null group by b.emp_no ;

注意： != 和=用来判断具体的值，而NULL需要用is或者is not判断
SQL11 获取所有员工当前的manager

文章图片

文章图片

我的思路：筛选思路，先将dept_emp和dept_manager通过dept_no连接起来，并要求emp_no不相等（去除管理者），然后筛选出dept_manager中员工编号emp_no不为空对应的两表中的员工编号即可。
我的题解：

select a.emp_no, b.emp_no from dept_emp as a left join dept_manager as b on a.dept_no = b.dept_no and a.emp_no != b.emp_no where b.emp_no is not null ;

涉及知识点：

完整sql执行顺序（每天看一遍，不信记不住）：
from -> where -> group by -> having -> select -> order by -> limit
join 连接表

提交结果：

文章图片

其他题解：
同样思路，可以直接join两表。

select a.emp_no, b.emp_no from dept_emp as a join dept_manager as b on a.dept_no = b.dept_no and a.emp_no != b.emp_no;

SQL12 获取每个部门中当前员工薪水最高的相关信息（困难）

文章图片

文章图片

我的思路：这里稍微有些复杂，首先需要取出的是当前每个部门中薪水最高的员工部门编号、员工编号、员工薪水，所以我们需要将dept_emp、salaries两表连接，限制to_date=\'9999-01-01\'表示当前员工，然后where进行筛选最大薪水，这里用了一个字查询去找每个部门的最高薪水（其实就是从连接表中分组筛选出每组的最大薪水）。
我的题解：

select dept_no, emp_no, salary from ( select a.dept_no, a.emp_no, b.salary from dept_emp as a join salaries as b on a.emp_no = b.emp_no and a.to_date=\'9999-01-01\' and b.to_date=\'9999-01-01\') as a2 where a2.salary = ( select max(b.salary) from dept_emp as a join salaries as b on a.emp_no = b.emp_no and a.to_date=\'9999-01-01\' and b.to_date=\'9999-01-01\' where a2.dept_no = a.dept_no group by a.dept_no ) order by dept_no ;

涉及知识点：

完整sql执行顺序（每天看一遍，不信记不住）：
from -> where -> group by -> having -> select -> order by -> limit
子查询
max聚合函数
group by 分组，其中非group by 的列在select里出现就必须是在聚合函数里。

提交结果：

文章图片

SQL15 查找employees表emp_no与last_name的员工信息

文章图片

我的思路：这个从题目标题无法看出需求，需要具体看描述：查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列。直接按照需求写sql即可，这里判断员工编号是否为奇数，可以使用%对2取余数进行判断，余数为1即为奇数。
我的题解：

select * from employees where emp_no % 2 = 1 and last_name != \'Mary\' order by hire_date desc ;

涉及知识点：

完整sql执行顺序（每天看一遍，不信记不住）：
from -> where -> group by -> having -> select -> order by -> limit
sql 取余数可以直接使用 %
order by hire_date desc 按hire_date列降序排序

提交结果：

文章图片

其他题解：

select * from employees where emp_no& 1 = 1 and last_name != \'Mary\' order by hire_date desc ;

这里判断是否为奇数，也可以使用位运算进行判断，任何数和1的位运算结果如果是1，则是奇数，否则运算结果为0，则为偶数。
这个是真的涨见识了，位运算很好的应用实践。
SQL16 统计出当前各个title类型对应的员工当前薪水对应的平均工资

文章图片

文章图片

我的思路：比较常规，首先明确需求：结果给出title以及平均工资avg，并且以avg升序排序，所以先将titles和salaries进行join连接，然后根据title进行分组求salary的平均值即可，最后按照平均值进行升序排序。
我的题解：

select title, avg(salary) as avg_s from ( select a.emp_no, a.title, b.salary from titles as a join salaries as b on a.emp_no = b.emp_no and a.to_date = b.to_date and a.to_date = \'9999-01-01\') as c group by title order by avg_s ;

涉及知识点：

完整sql执行顺序（每天看一遍，不信记不住）：
from -> where -> group by -> having -> select -> order by -> limit
窗口函数：avg使用
group by 分组
order by 排序，默认是升序

提交结果：

文章图片

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

文章图片

我的思路：这个只有一个表，蛮简单的，直接按照salary降序排序，取出第二列数据即可。
我的题解：

select emp_no, salary from salaries where to_date = \'9999-01-01\' order by salary desc limit 1,1 ;

涉及知识点：

完整sql执行顺序（每天看一遍，不信记不住）：
from -> where -> group by -> having -> select -> order by -> limit
order by salary desc 降序排序
从指定位置开始取出指定行：limit m,n 从m+1行开始，取出n行数据

提交结果：

文章图片

其他题解：
上面解法有局限性，如果排名第一的有很多个，那么取出来的还是薪水最高的，所以好的方法应该是直接按第二的薪水进行筛选。
所以我们先找出第二薪水是多少，然后筛选出薪水等于第二薪水的行即可。

select emp_no, salary from salaries where to_date = \'9999-01-01\' and salary = ( select salary from salaries group by salary order by salary desc limit 1,1 ) order by salary desc ;

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

文章图片

文章图片

我的思路：紧接上一题，和sql17的区别就是将员工薪水另外存了一张表，另外要求不能使用order by 排序。我们可以先找出薪水表中第二高的薪水，然后从employees、salaries两表中筛选出薪水第二高的员工信息。
取第二高的薪水，这里采用的是去除当前最高薪水后，再查找剩余薪水中的最高薪水。
我的题解：

select a.emp_no, b.salary, a.last_name, a.first_name from employees as a join salaries as b on a.emp_no = b.emp_no and b.salary = ( select max(salary) from salaries where to_date = \'9999-01-01\' and salary < ( select max(salary) from salaries where to_date = \'9999-01-01\' ) ) ;

涉及知识点：

完整sql执行顺序（每天看一遍，不信记不住）：
from -> where -> group by -> having -> select -> order by -> limit
窗口函数max取最大值

提交结果：

文章图片

其他题解：

文章图片

这里有个大神三玫提供了另外一种找第二高薪水的方法，通过salaries自连接，连接条件是

s1.salary &
lt;
= s2.salary

，这样后，再根据s1.salary分组，然后count(distinct s2.salary) = 2就是第二高薪水。

select a.emp_no, b.salary, a.last_name, a.first_name from employees as a join salaries as b on a.emp_no = b.emp_no and b.salary = ( select s1.salary from salaries as s1 join salaries as s2 on s1.salary < = s2.salary and s1.to_date = s2.to_date and s1.to_date = \'9999-01-01\' group by s1.salary having count(distinct s2.salary) = 2 ) ;

这个思路真的特别巧妙，刚开始你可能会比较懵，不知道为什么这样就可以筛选出第二高薪水了，但是你一画图，举例子操作下，瞬间就能明白。
这里的原理可以归纳为：大于等于自己的数值的个数一定等于自己的降序排序的位数。
例如：在集合{100,98,97,89}中，大于等于98的只有100，98，所以98在这个集合中必然是第二大的数。

文章图片

评论区也有人做了效率对比，当数据量上升时，该方法效率会大大降低，其实原因也很简单，salaries变大，自连接自然会消耗大量时间。

文章图片

SQL19 查找所有员工的last_name和first_name以及对应的dept_name

文章图片

文章图片

我的思路：这个题目和sql5是类似的，现在多了一个departments表，输出里把dept_no改成了dept_name，所以在原来的解法中，dept_emp表先与departmentsjoin，然后再和employees连接即可。

文章图片

我的题解：

select a.last_name, a.first_name, b.dept_name from ( select emp_no, last_name, first_name from employees) as a left join ( select d1.emp_no, d2.dept_name from dept_emp as d1 join departments as d2 on d1.dept_no = d2.dept_no ) as b on b.emp_no = a.emp_no ;

涉及知识点：

完整sql执行顺序（每天看一遍，不信记不住）：
from -> where -> group by -> having -> select -> order by -> limit
left join

提交结果：

文章图片

SQL21 查找在职员工自入职以来的薪水涨幅情况

文章图片

文章图片

我的思路：这里需要求每个在职员工从入职开始到现在的薪水涨幅情况，这里直接找出每个在职员工入职时薪水，和当前薪水，然后求差即可。
这题比较有意思的是需要理解清楚以下几个日期字段含义：

employees.hire_date：表示雇佣日期，可以理解为入职日期；
salaries.from_date：表示薪水开始时间；
salaries.to_date：表示薪水结束时间，如果为9999-01-01，则表示为当前在职员工。

我的题解：

select s1.emp_no, (s2.salary-s1.salary) as growth from ( -- 入职薪水 select a1.emp_no, a2.salary from employees as a1 join salaries as a2 on a1.emp_no = a2.emp_no and a1.hire_date = a2.from_date ) as s1 join ( -- 当前薪水 select emp_no, salary from salaries where to_date = \'9999-01-01\' ) as s2 on s1.emp_no = s2.emp_no order by growth ;

涉及知识点：

完整sql执行顺序（每天看一遍，不信记不住）：
from -> where -> group by -> having -> select -> order by -> limit
join 连接
order by 分组

提交结果：

文章图片

SQL22 统计各个部门的工资记录数

文章图片

文章图片

我的思路：需求搞清楚，就蛮简单了，先将部门员工表和薪水表连接，然后通过部门编号分组，计算数据条数，可以得出每个部门对应的薪水记录数，然后和部门表连起来，即可获取对应的部门名称了。
我的题解：

select a1.dept_no, b1.dept_name, a1.sum from ( select a.dept_no, count(a.emp_no) as sum from dept_emp as a join salaries as b on a.emp_no = b.emp_no group by a.dept_no) as a1, departments as b1 where a1.dept_no = b1.dept_no ;

涉及知识点：

完整sql执行顺序（每天看一遍，不信记不住）：
from -> where -> group by -> having -> select -> order by -> limit
窗口函数count计算分组内数据条数
group by 分组

提交结果：

文章图片

SQL23 对所有员工的薪水按照salary降序进行1-N的排名

文章图片

我的思路：这里正好用到之前讲的窗口函数dense_rank。按题目示例我们知道，需要输出的是按薪水降序排序，生成新的一列排序序号，相同薪水排序序号相同，且按照emp_no升序排列。
因为做这题前一天才看了窗口函数相关内容，所以马上想到dense_rank，可以计算排序，相同位次一样，如：

数值：23 23 24 25 排序：1123

我的题解：

select emp_no, salary, dense_rank() over(order by salary desc) as t_rank from salaries order by t_rank,emp_no;

涉及知识点：

完整sql执行顺序（每天看一遍，不信记不住）：
from -> where -> group by -> having -> select -> order by -> limit
窗口函数 dense_rank() over(order by salary desc)
顺便提一下：rank() 会跳位，比如还是上面的例子，排序会变成：1 1 3 4
顺便提一下：row_number() 直接为位置序号，比如还是上面的例子，排序会变成：1 2 3 4

提交结果：

文章图片

其他题解：

文章图片

有点算法的意思了，下回给大家分析下，也欢迎大家评论区说说自己的看法。
SQL24 获取所有非manager员工当前的薪水情况

文章图片

文章图片

我的思路：和之前的题目很相像，越往后会发现，给的表越来越多，信息还是一样，需求也一样，这样也更符合数据库范式。这里我直接从dept_emp和salaries中取出所需数据，去除部门经理即可，类似把现在的散表合并成之前的宽表，这里没有用到employees表，根据需求确实不需要。
我的题解：

-- employees 员工信息表 -- dept_emp 部门员工关系表 -- dept_manager 部门经理信息表 -- salaries 员工薪水表select a.dept_no, a.emp_no, b.salary from dept_emp as a,salaries as b where a.emp_no not in (select emp_no from dept_manager) and a.to_date = \'9999-01-01\' and a.emp_no = b.emp_no ;

涉及知识点：