牛客网sql实战题库题解（一）
徐梓荐
中国人民大学 软件工程硕士
4 人赞同了该文章
❝ 作为数据分析师，取数可能来自外部需求（虽然大家都不想当sqlboy or sqlgirl），也可能来自于自己分析的需求，因此SQL算是日常工作接触的最多一门编程语言（姑且算作编程语言吧，不要杠我，有不同意见就是你说的对）。因此在面试时，SQL一定是其中一项会被考察的技术，牛客网题库共61道题，题目虽然没有包含常见的业务场景，但是也涵盖了大部分常用的语法及思路，可以用作SQL入门或平时练习。本系列专栏预计共10期，每期10道题，力求详细讲解题目的各类思路，以及平时写SQL的一些调优的小trick，本文为第一期。
❞
001 查找最晚入职员工的所有信息
题目描述：查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天.
思路讲解：通过ORDER BY语句排序，DESC为降序，ASC为升序，以降序排列只取第一条记录。
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`));
参考答案：
SELECT *FROM employeesORDER BY hire_date DESC LIMIT 1;
002 查找入职员工时间排名倒数第三的员工所有信息
查找入职员工时间排名倒数第三的员工所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天
思路讲解：
① 使用LIMIT语句，对应参考答案1。本方法只能筛选出一条记录，而且这种LIMIT语句的语法不一定适用所有数据库（「hive不支持」）。虽然本题为了使题的难度降低，假定了所有员工入职时间都不是同一天，但在实际的业务场景下这种理想情况几乎不存在，因此还是需要考虑有多名员工入职日期为同一天的情况；
② 窗口函数的方式，对应参考答案2。根据hire_date进行降序排序，给定每一条记录一个排名rank，筛选rank=3的记录。严格来讲，本题应该使用dense_rank窗口函数。
❝ 常见用于排名的窗口函数区别（rank、row_number、dense_rank）:
rank: 相同排名的记录会并列，并列的数字会被跳过；dense_rank: 相同排名的记录会并列，且并列的数字会被跳过；row_number: 不会显示并列的数字；
③ 子查询的方式，对应参考答案3: 在子查询中查询到倒数第三的入职时间（去重后），外层查询限制hire_date=子查询的结果。这种方式的缺点是，hive不支持IN子查询，这种方式会报错，而hive几乎是当前大部分互联网公司查询数据的工具；
④ 单表关联查询的方式，对应参考答案4: 大部分子查询的SQL，其实都能改写成单表关联的方式，其实本题没有必要通过关联查询的方式实现，主要是为了讲解思路。
参考答案1：
select * from employees order by hire_date desc limit 2,1
参考答案2：
SELECT a.emp_no, a.birth_date, a.first_name, a.last_name, a.gender , a.hire_dateFROM ( SELECT emp_no, birth_date, first_name, last_name, gender , hire_date, dense_rank OVER (ORDER BY hire_date DESC) AS rank FROM employees) aWHERE a.rank = 3;
参考答案3：
SELECT *FROM employeesWHERE hire_date IN ( SELECT DISTINCT hire_date FROM employees ORDER BY hire_date DESC LIMIT 2, 1);
参考答案4：
SELECT e.*FROM employees e JOIN ( SELECT DISTINCT hire_date FROM employees ORDER BY hire_date DESC LIMIT 2, 1 ) m ON e.hire_date = m.hire_date;
003 查找当前薪水详情以及部门编号dept_no
查找各个部门当前(dept_manager.to_date='9999-01-01')领导当前(salaries.to_date='9999-01-01')薪水详情以及其对应部门编号dept_no (注:请以salaries表为主表进行查询，输出结果以salaries.emp_no升序排序，并且请注意输出结果，dept_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`));CREATE TABLE `dept_manager` (`dept_no` char(4) NOT NULL, — '部门编号'`emp_no` int(11) NOT NULL, — '员工编号'`to_date` date NOT NULL,PRIMARY KEY (`emp_no`,`dept_no`));
思路讲解: 本题应该没有太多的思路可解，比较明显的用两个表关联进行查询，限定题目要求的当前时间即可（hire_date='9999-01-01'）;
❝ 本题可以直接两表关联（参考答案2），关联后再对时间进行限制。但是在互联网场景下，通常是通过hive在大数据量上进行查询，如果两个表直接关联的话代价是非常大的，而且部分公司对数据查询工具的审计条件都包含「对分区表进行查询时必须指定分区」的限制策略，目的是确保SQL不会大量占用集群资源。参考答案的写法是常见的一种SQL调优的方式，即在关联前对表的数据量进行限制。
参考答案1：
SELECT a.*, b.dept_noFROM ( SELECT * FROM salaries WHERE to_date = '9999-01-01') a INNER JOIN ( SELECT emp_no, dept_no FROM dept_manager WHERE to_date = '9999-01-01' ) b ON a.emp_no = b.emp_no;
参考答案2：
SELECT s.*, d.dept_noFROM salaries s INNER JOIN dept_manager d ON s.emp_no = d.emp_noWHERE s.to_date = '9999-01-01' AND d.to_date = '9999-01-01';
004 查找所有已经分配部门的员工的last_name和first_name以及dept_no
查找所有已经分配部门的员工的last_name和first_name以及dept_no(请注意输出描述里各个列的前后顺序)
CREATE TABLE `dept_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 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`));
思路讲解：本题也是关联查询的思路，内连接INNER JOIN只会返回两个关联表能关联到的记录，因此只会返回已分配部门的员工。由于没有任何条件的限制，所以也没有调优的空间了。
参考答案：
SELECT e.last_name, e.first_name, dt.dept_noFROM ( SELECT last_name, first_name, emp_no FROM employees) e INNER JOIN dept_tmp dt ON e.emp_no = dt.emp_no;
❝ ps: 有些同学可能注意到，我在写本题的SQL时，没有直接用employees表进行关联，而是只选择了部分需要的字段（关联字段，或者题目要求的字段）。这样做的原因是，在实际的业务中，一个表动辄有几十上百个字段，如果直接SELECT *是对集群资源极大的浪费，因此一般都只查询需要的字段，虽然本题字段少，没有必要这么做，但是在平时练习时应该养成良好的编程习惯。
❞
005 查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工
查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工，与004题使用相同的两个表(请注意输出描述里各个列的前后顺序)。
思路讲解：与004题类似，区别是005题要求查询出来没有分配具体部门的员工，因此应该用左连接LEFT JOIN，在部门表中关联不到的记录（即未分配部门的员工），dept_no会返回空值。
参考答案：
SELECT e.last_name, e.first_name, d.dept_noFROM ( SELECT last_name, first_name, emp_no FROM employees) e LEFT JOIN ( SELECT dept_no, emp_no FROM dept_emp ) d ON e.emp_no = d.emp_no;
006 查找所有员工入职时候的薪水情况
查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序(请注意，一个员工可能有多次涨薪的情况)
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`));
思路讲解：
① 比较直观，也比较常规，也是第一反应的思路：两表连接，对应参考答案1，连接条件为员工编号相等，员工的入职时间=薪资的起始时间；
② 本题本意其实也是引导大家练习表连接思路及语法的使用，但细想一下就会发现题目中包含的“bug”: 薪资表salaries已经包含了题目要求的emp_no和salary字段，且同一员工有多次涨薪记录，那么同一员工的记录中，from_date起始时间最早的记录其实就是入职的薪资，不需要再关联员工表了。
参考答案2，通过emp_no分组，在HAVING子句中限制记录为MIN(from_date);参考答案3，rank窗口函数，以emp_no分组，from_date升序排序，rank=1即为最早的记录；
参考答案1：
SELECT e.emp_no, s.salaryFROM ( SELECT emp_no, hire_date FROM employees) e LEFT JOIN ( SELECT salary, from_date FROM salaries ) s ON e.emp_no = s.emp_no AND e.hire_date = s.from_dateORDER BY e.emp_no DESC;
参考答案2：
SELECT s.emp_no, s.salaryFROM ( SELECT salary, from_date FROM salaries) sGROUP BY s.emp_noHAVING MIN(s.from_date)ORDER BY s.emp_no DESC
参考答案3：
SELECT m.emp_no, m.salaryFROM ( SELECT emp_no, salary, rank OVER (PARTITION BY emp_no ORDER BY from_date ASC) AS rank FROM salaries) mWHERE m.rank = 1ORDER BY m.emp_no DESC
007 查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t
题目描述如题，思路讲解：同一员工的一条记录为一次薪水变动，按照题意根据emp_no分组，组内记录大于15条即满足条件。严格来讲，第一条记录，即最早的、入职时的工资记录，不属于薪水变动，因此在查询时应该用COUNT-1，且COUNT大于16作为筛选条件，但是在牛客的系统上这种解法不会通过（摊手）。
参考答案：
SELECT s.emp_no, COUNT(*) AS tFROM ( SELECT emp_no FROM salaries) sGROUP BY s.emp_noHAVING COUNT(*) 15
008 找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况
找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示
思路讲解：题目要求只需要对salary用distinct进行去重，并按照salary降序排列即可。去重也可以用窗口函数（参考答案2），不过duck不必。
参考答案1：
SELECT distinct s.salaryFROM ( SELECT salary, to_date FROM salaries) sWHERE s.to_date='9999-01-01'ORDER BY s.salary DESC;
参考答案2：
SELECT m.salaryFROM(SELECT salary, row_number over(PARTITION BY salary) as rankFROM salariesWHERE to_date='9999-01-01') m WHERE m.rank=1ORDER BY m.salary DESC;
009 获取所有部门当前manager的当前薪水情况
获取所有部门当前(dept_manager.to_date='9999-01-01')manager的当前(salaries.to_date='9999-01-01')薪水情况，给出dept_no, emp_no以及salary(请注意，同一个人可能有多条薪水情况记录)
CREATE TABLE `dept_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 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`));
思路讲解：本题思路与003题一致，都是表连接且都是内连接，甚至调优方式都相同。
❝ SQL新手可能遇到此类问题，不知道如何上手去写，因此我在这里以本题为例，讲一种平时我自己写SQL的方式，通过「拆解」的方式把一个大的问题拆解成小问题：
第一步，获取当前部门的manager：
SELECT dept_no, emp_no
FROM dept_manager
WHERE to_date = '9999-01-01'
第二步，获取当前薪资：
SELECT emp_no, salary
FROM salaries
WHERE to_date = '9999-01-01'
第三步，关联两个拆解出来的SQL：
判断关联条件（本题为员工编号emp_no），及关联类型（本题为内连接，当前manager一定对应一条当前薪资）
参考答案：
SELECT d.dept_no, d.emp_no, s.salaryFROM ( SELECT dept_no, emp_no FROM dept_manager WHERE to_date = '9999-01-01') d INNER JOIN ( SELECT emp_no, salary FROM salaries WHERE to_date = '9999-01-01' ) s ON d.emp_no = s.emp_no
010 获取所有非manager的员工emp_no
CREATE TABLE `dept_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 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`));
题目描述如题，思路讲解：
① 表连接：由于互联网公司大部分取数的场景都是通过hive实现，而hive对子查询的支持有限，因此类似的查询需求应优先用连接的方式。
本题依然可以用「拆解」的思路:
先找到所有员工，SELECT emp_no FROM employees再找到「当前」所有的manager
SELECT emp_noFROM dept_managerWHERE to_date = '9999-01-01'
确定关联字段及类型：
关联字段是emp_no，关联方式上的选择应选择左连接LEFT JOIN，以员工表为主表进行关联，在dept_no表中关联不到（dept_no.emp_no为空），说明该员工不是manager。
② 子查询，对应参考答案2.
参考答案1：
SELECT e.emp_noFROM ( SELECT emp_no FROM employees) e LEFT JOIN ( SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01' ) d ON e.emp_no = d.emp_noWHERE d.emp_no IS NULL;
参考答案2：
SELECT e.emp_noFROM ( SELECT emp_no FROM employees) eWHERE e.emp_no NOT IN ( SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01');
http://weixin.qq.com/r/4zu-pznEYWQerX3E927x (二维码自动识别)
编辑于 2020-07-12
