牛客网sql题库（1-30题）—— 个人答案与过程解析

本篇博客分享一下我在牛客网sql题库刷题时，自己敲出来的结果。结果均通过了牛客网的结果验证。
具体的题目就不放在博客中了，感兴趣可以自行去搜索牛客网，找到sql题库在线编程

每道题下面都有我自己写的题目解析，可以有助于理解。

题目 1 - 10

（1） 查找employees里最晚入职员工的所有信息

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

解析：where语句+子查询，具体的子查询种类可以看
子查询（7种类型）

（2） 查找employees里入职员工时间排名倒数第三的员工所有信息

select * 
from employees 
order by hire_date desc 
limit 2,1;

解析：limit用法

limit y 分句表示: 读取 y 条数据
limit x, y 分句表示: 跳过 x 条数据，读取 y 条数据
limit y offset x 分句表示: 跳过 x 条数据，读取 y 条数据

select * from stu1;
select * from stu1 limit 0,1;
select * from stu1 limit 1,1;
select * from stu1 limit 1 offset 2;

结果如下图所示

（3）查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序

select salaries.*,dept_manager.dept_no from dept_manager
left join salaries
on dept_manager.emp_no = salaries.emp_no
group by salaries.emp_no;

解析：提取关键字，各个部门，则group by后面字段为部门编号，进行分组
结果默认时以升序排序，这里可以不用给出

（4）查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示

select last_name,first_name,dept_no
from dept_emp left join employees
on dept_emp.emp_no = employees.emp_no;

解析：表连接即可解决，但是要注意用部门表作为左表。如果以员工表作为左表，那么未分配部门也会被显示出来，只不过部门号变成null

（5）查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工

select last_name,first_name,dept_no from employees
left join dept_emp on employees.emp_no = dept_emp.emp_no;

解析：与上一题正好相反，用员工表作为左表即可

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

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

解析：having的用法，用在分组之后对分组结果进行筛选

（8）找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

select DISTINCT salary 
from salaries 
order by salary desc;

解析：逆序显示用order by …… desc，相同的薪水只显示依次说明要针对薪水字段去重，distinct即可

（10）找出所有非部门领导的员工emp_no

select employees.emp_no 
from employees 
left join dept_manager 
on employees.emp_no = dept_manager.emp_no
where dept_manager.emp_no is null;

解析：员工表和部门领导表进行连接，然后拿员工号和部门领导的员工号（简称为领导号）进行连接，如果相同则连接成功，如果只有员工号没有领导号，那么该行数据的领导号字段值为null。利用这一点进行筛选即可。

题目 11 - 20

（11）获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示

select dept_emp.emp_no,dept_manager.emp_no as manager from dept_emp left join dept_manager 
on dept_emp.dept_no = dept_manager.dept_no
where dept_emp.emp_no != dept_manager.emp_no;

解析：按照dept_no进行连接，同一个部门的员工号和经理的员工号（简称为经理号）会被连接至同一行，如果员工号和经理号相同说明该名员工是经理，如果不同则是普通员工。

（12）获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列

select dd.dept_no,dd.emp_no,ss.maxSalary
from (select d1.emp_no,d1.dept_no,salary from dept_emp as d1,salaries as s1 where d1.emp_no=s1.emp_no) as dd,
(select dept_no,max(salary) as maxSalary from dept_emp as d2,salaries as s2
where d2.emp_no = s2.emp_no
group by dept_no) as ss
where dd.dept_no = ss.dept_no and dd.salary=ss.maxSalary
order by dd.dept_no;

解析：这题我之前写过详细的解析，在这篇博客里面MySQL练习 —— 牛客网SQL第十二题解法（求取每个分组的最大值）

（15）查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

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

解析：为奇数说明%2的结果是1，用<>或者!=都可以表达不等于的意思

（16）统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg，并且以avg升序排序

select title,avg(salary) as a from titles as t,salaries as s
where t.emp_no = s.emp_no
group by title
order by a;

解析：依旧是先进行表连接，然后按照字段分组，求取分组的平均值
关于别名的使用时机

（17）获取薪水第二多的员工的emp_no以及其对应的薪水salary

select emp_no,salary 
from salaries 
order by salary desc 
limit 1,1;

解析：先排序后筛选

（18）查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成

select e.emp_no,a.salary,last_name,first_name from employees as e,(select emp_no,salary from salaries where salary=(select max(salary) as m from salaries 
where salary < (select max(salary) from salaries))) as a
where e.emp_no = a.emp_no;

解析：这题我之前也总结过 MySQL笔记 —— 求分组前几名问题总结（单表求topn，多表求topn，索引求topn）

（19）查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

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

解析：这里因为dept_name和last_name，first_name字段不在同一张表中。两张表没有可以连接的字段，所以需要借助第三张表进行连接，部门员工关系表。首先员工表和部门员工关系表进行连接，提取员工号，姓名，部门号字段。再拿查询到的临时表与部门表进行连接，获取部门名称。

题目 21 - 30

（21）查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

select a.emp_no, (b.salary - a.salary) as growth
from(
	select s.emp_no, s.salary
	from employees as e, salaries as s
	where e.emp_no = s.emp_no and e.hire_date = s.from_date
) as a,
(
	select emp_no, salary
	from salaries
	where to_date = '9999-01-01'
) as b
where a.emp_no = b.emp_no
order by growth

解析：要求取薪水涨幅情况，那么就需要最后一次的薪资减去第一次的薪资。这里的 a 表获取的是员工编号和第一次薪资，将员工表和薪资表进行连接，找到入职那天hire_date对应的薪资，即第一次的薪资。 b 表获取的是员工编号和最后一次的薪资，b 表不用连接，日期等于’9999-01-01’说明一直在职，并且对应的就是最后一次的薪资。
最后将 a表和 b表按员工号连接，在select子句中相减即可得出结果。

（22）统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序

select d1.dept_no,dept_name,count(salary) as sum
from (select dept_emp.emp_no,dept_no,salary from dept_emp,salaries where dept_emp.emp_no=salaries.emp_no) as d1,
departments as d2
where d1.dept_no=d2.dept_no group by d1.dept_no
order by d1.dept_no;

解析：这题主要是信息分别存在不同的表中，所以要想全部获取题目中要求的信息，就需要进行多表连接。先是部门员工关系表与薪资表连接，获取员工编号，部门编号，薪资。将两表连接后的查询结果作为临时表与部门表进行连接，获取部门名称。
按照部门进行分组，然后统计薪资记录的数量即可。

（23）对所有员工的薪水按照salary降序进行1-N的排名，要求相同salary并列，且按照emp_no升序排列

select a.emp_no,salary,t_rank from (
		select emp_no,count(*) as t_rank
		from salaries as s1,(select salary from salaries group by salary) as s2
		where s1.salary <= s2.salary
		group by emp_no) as a,salaries as b 
where a.emp_no=b.emp_no
order by t_rank;

解析：我一开始看见这个题目，就直接相同表取不同别名，然后自连接按salary进行比较。但是这里的salary是有相同值的，所以不能用相同表。这里将一张原表与一张按salary去重后的表进行连接，然后再按照salary进行比较就行了

（24）获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary

select e.dept_no,e.emp_no,salaries.salary from (select a.emp_no,a.dept_no 
from (select employees.emp_no,dept_no from employees left join dept_emp on employees.emp_no=dept_emp.emp_no) as a 
left join dept_manager on a.dept_no=dept_manager.dept_no
where a.emp_no <> dept_manager.emp_no) as e 
left join salaries on e.emp_no=salaries.emp_no
where salaries.salary is not null;

解析：这题的主要难点在于有四张表，所以要一张表一张表的连接，为了方便理解，我把代码拆分一下
首先将员工表和员工部门关系表进行连接，然后获取员工号和部门编号

select employees.emp_no,dept_no 
from employees 
left join dept_emp on employees.emp_no=dept_emp.emp_no;

有了这张表，就可以继续与部门经理表进行连接，找出所有不是经理的员工

select a.emp_no,a.dept_no 
from (select employees.emp_no,dept_no from employees left join dept_emp on employees.emp_no=dept_emp.emp_no) as a 
left join dept_manager on a.dept_no=dept_manager.dept_no
where a.emp_no <> dept_manager.emp_no ;

最后将上面的查询结果临时表与薪资表进行连接，获取每个非经理员工的薪资

（25）获取员工其当前的薪水比其manager当前薪水还高的相关信息，
第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

select distinct a.emp_no,a.manager_no,emp_salary,manager_salary
from (select d.emp_no,manager_no,salary as emp_salary 
			from (select dept_emp.emp_no,dept_emp.dept_no,dept_manager.emp_no as manager_no 
					from dept_emp 
					left join dept_manager on dept_emp.dept_no=dept_manager.dept_no
					where dept_emp.emp_no <> dept_manager.emp_no) as d 
					left join salaries on d.emp_no = salaries.emp_no) as a,
			(select dept_manager.emp_no as manager_no, salary as manager_salary
					from dept_manager,salaries
					where dept_manager.emp_no=salaries.emp_no) as b
where a.manager_no = b.manager_no and emp_salary>manager_salary;

解析：这题写起来也挺复杂的，我也是将代码拆分成几部分发出来

-- 获取所有员工
select dept_emp.emp_no,dept_emp.dept_no,dept_manager.emp_no as manager_no 
from dept_emp left join dept_manager on dept_emp.dept_no=dept_manager.dept_no
where dept_emp.emp_no <> dept_manager.emp_no;

-- 获取所有员工的薪水
select d.emp_no,manager_no,salary as emp_salary 
from (
			select dept_emp.emp_no,dept_emp.dept_no,dept_manager.emp_no as manager_no 
			from dept_emp 
			left join dept_manager on dept_emp.dept_no=dept_manager.dept_no
			where dept_emp.emp_no <> dept_manager.emp_no) as d 
left join salaries on d.emp_no = salaries.emp_no
where salary is not null;

-- 获取所有经理的薪水
select dept_manager.emp_no as manager_no, salary as manager_salary
from dept_manager,salaries
where dept_manager.emp_no=salaries.emp_no;

-- 将员工薪水和经理薪水放在一起
-- 先写个模板
select a.emp_no,a.manager_no,emp_salary,manager_salary
from () as a,() as b
where a.manager_no = b.manager_no

-- 代入
select distinct a.emp_no,a.manager_no,emp_salary,manager_salary
from (select d.emp_no,manager_no,salary as emp_salary 
			from (select dept_emp.emp_no,dept_emp.dept_no,dept_manager.emp_no as manager_no 
					from dept_emp 
					left join dept_manager on dept_emp.dept_no=dept_manager.dept_no
					where dept_emp.emp_no <> dept_manager.emp_no) as d 
					left join salaries on d.emp_no = salaries.emp_no) as a,
			(select dept_manager.emp_no as manager_no, salary as manager_salary
					from dept_manager,salaries
					where dept_manager.emp_no=salaries.emp_no) as b
where a.manager_no = b.manager_no and emp_salary>manager_salary;

（26）汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count，结果按照dept_no升序排序，dept_no一样的再按title升序排序

select dept_no,dept_name,title,count(title) as count
from (select a.emp_no,a.dept_no,dept_name,title
		from (select emp_no,dept_emp.dept_no,dept_name 
			from dept_emp left join departments 
			on dept_emp.dept_no=departments.dept_no) as a,titles as b 
where a.emp_no=b.emp_no) as e 
group by dept_no,title
order by dept_no,title;

解析：还是将代码拆分一下，把我思考的过程发出来，这样有助于理解

-- 员工信息加上部门名称
select emp_no,dept_emp.dept_no,dept_name 
from dept_emp 
left join departments on dept_emp.dept_no=departments.dept_no

-- 再加上职称
select a.emp_no,a.dept_no,dept_name,title
from (select emp_no,dept_emp.dept_no,dept_name 
			from dept_emp left join departments 
			on dept_emp.dept_no=departments.dept_no) as a,titles as b 
where a.emp_no=b.emp_no

-- 统计title类型数目
select dept_no,dept_name,title,count(title) as count
from (select a.emp_no,a.dept_no,dept_name,title
		from (select emp_no,dept_emp.dept_no,dept_name 
			from dept_emp left join departments 
			on dept_emp.dept_no=departments.dept_no) as a,titles as b 
where a.emp_no=b.emp_no) as e 
group by dept_no,title;

-- 先按照dept_no升序排序，后在dept_no内部再按照title升序排序
select dept_no,dept_name,title,count(title) as count
from (select a.emp_no,a.dept_no,dept_name,title
		from (select emp_no,dept_emp.dept_no,dept_name 
			from dept_emp left join departments 
			on dept_emp.dept_no=departments.dept_no) as a,titles as b 
where a.emp_no=b.emp_no) as e 
group by dept_no,title
order by dept_no,title;

（29）使用join查询方式找出没有分类的电影id以及名称

select film.film_id,title
from film left join film_category on film.film_id=film_category.film_id
where category_id is null;

解析：正常的表连接即可求出结果

（30）使用子查询的方式找出属于Action分类的所有电影对应的title,description

select title,description
from film left join film_category 
on film.film_id=film_category.film_id and category_id = (select category_id from category where name='Action')
where category_id is not null;

解析：首先获取属于Action分类的所有电影的电影分类id，然后将电影表film与film_category 表进行连接，获取title,description字段信息，要求两表连接后的每一行数据的电影分类id在之前求取的范围之内。