相关文章推荐
空虚的西瓜  ·  Error querying ...·  2 周前    · 
含蓄的消防车  ·  consul tags ...·  10 月前    · 
光明磊落的镜子  ·  [转](SQL Server) ...·  1 年前    · 
牛客网sql实战题库题解(一)

牛客网sql实战题库题解(一)

❝ 作为数据分析师,取数可能来自外部需求(虽然大家都不想当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 employees
ORDER 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_date
FROM (
 SELECT emp_no, birth_date, first_name, last_name, gender
  , hire_date, dense_rank() OVER (ORDER BY hire_date DESC) AS rank
 FROM employees
WHERE a.rank = 3;

参考答案3:

SELECT *
FROM employees
WHERE 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
 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_no
FROM (
 SELECT *
 FROM salaries
 WHERE to_date = '9999-01-01'
 INNER JOIN (
  SELECT emp_no, dept_no
  FROM dept_manager
  WHERE to_date = '9999-01-01'
 ON a.emp_no = b.emp_no;

参考答案2:

SELECT s.*, d.dept_no
FROM salaries s
 INNER JOIN dept_manager d
 ON s.emp_no = d.emp_no
WHERE 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_no
FROM (
 SELECT last_name, first_name, emp_no
 FROM employees
 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_no
FROM (
 SELECT last_name, first_name, emp_no
 FROM employees
 LEFT JOIN (
  SELECT dept_no, emp_no
  FROM dept_emp
 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.salary
FROM (
 SELECT emp_no, hire_date
 FROM employees
 LEFT JOIN (
  SELECT salary, from_date
  FROM salaries
 ON e.emp_no = s.emp_no
  AND e.hire_date = s.from_date
ORDER BY e.emp_no DESC;

参考答案2:

SELECT s.emp_no, s.salary
FROM (
  SELECT salary, from_date
  FROM salaries
GROUP BY s.emp_no
HAVING MIN(s.from_date)
ORDER BY s.emp_no DESC

参考答案3:

SELECT m.emp_no, m.salary
FROM (
 SELECT emp_no, salary, rank() OVER (PARTITION BY emp_no ORDER BY from_date ASC) AS rank
 FROM salaries
WHERE m.rank = 1
ORDER BY m.emp_no DESC

007 查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t

题目描述如题,思路讲解:同一员工的一条记录为一次薪水变动,按照题意根据emp_no分组,组内记录大于15条即满足条件。严格来讲,第一条记录,即最早的、入职时的工资记录,不属于薪水变动,因此在查询时应该用COUNT( )-1,且COUNT( )大于16作为筛选条件,但是在牛客的系统上这种解法不会通过(摊手)。

参考答案:

SELECT s.emp_no, COUNT(*) AS t
FROM (
  SELECT emp_no
  FROM salaries
GROUP BY s.emp_no
HAVING COUNT(*)>15

008 找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况

找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示

思路讲解:题目要求只需要对salary用distinct进行去重,并按照salary降序排列即可。去重也可以用窗口函数(参考答案2),不过duck不必。

参考答案1:

SELECT distinct s.salary
FROM (
  SELECT salary, to_date
  FROM salaries
WHERE s.to_date='9999-01-01'
ORDER BY s.salary DESC;

参考答案2:

SELECT m.salary
FROM(
SELECT salary, row_number() over(PARTITION BY salary) as rank
FROM salaries
WHERE to_date='9999-01-01'
WHERE m.rank=1
ORDER 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.salary
FROM (
 SELECT dept_no, emp_no
 FROM dept_manager
 WHERE to_date = '9999-01-01'
 INNER JOIN (
  SELECT emp_no, salary
  FROM salaries
  WHERE to_date = '9999-01-01'
 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_no
FROM dept_manager
WHERE to_date = '9999-01-01'
  • 确定关联字段及类型:
    关联字段是emp_no,关联方式上的选择应选择左连接LEFT JOIN,以员工表为主表进行关联,在dept_no表中关联不到(dept_no.emp_no为空),说明该员工不是manager。

② 子查询,对应参考答案2.

参考答案1:

SELECT e.emp_no
FROM (
 SELECT emp_no
 FROM employees
 LEFT JOIN (
  SELECT emp_no
  FROM dept_manager
  WHERE to_date = '9999-01-01'
 ON e.emp_no = d.emp_no
WHERE d.emp_no IS NULL;

参考答案2:

SELECT e.emp_no
FROM (
 SELECT emp_no
 FROM employees