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