Linux运维

牛客网 题库 运维

牛客网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

Similar Posts

发表评论

邮箱地址不会被公开。 必填项已用*标注