本文共 5421 字，大约阅读时间需要 18 分钟。

@Author：Runsen

@Date：2020/9/18

大四刷题拼offer系列，不拼不行啊

现在集中火力进军SQL，然后过渡到Java。下面的SQL题目都是来自牛客网，都是本人觉得挺重要的题目，然后记录下。

查找最晚入职员工的所有信息

这个题目的是入门的水平，就是目前所有的数据里员工入职的日期都不是同一天（sqlite里面的注释为–，在mysql中为comment）

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

还有一种方法激素使用子查询，针对的是最后一天的时间有多个员工信息：select * from employees where hire_date = (select max(hire_date) from employees);

查找入职员工时间排名倒数第三的员工所有信息

这个题目和上面变了，现在寻找倒数第三。那么就使用limit+offset 。offset 就是区间长度的意思，可以是一个数，也可以是一个区间，记得是从0开始，和Python列表完全一样。

下面举几个limit+offset 的示例。

以下的两种方式均表示取2,3,4三条条数据。1.select* from test LIMIT 1,3;当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量。2.select * from test LIMIT 3 OFFSET 1;(在mysql 5以后支持这种写法)当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 。

查找入职员工时间排名倒数第三的员工所有信息的SQl代码：select * from employees order by hire_date desc limit 1 offset 2

两表查询

题目描述：查找各个部门当前(dept_manager.to_date='9999-01-01')领导,当前(salaries.to_date='9999-01-01')薪水详情以及其对应部门编号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`));

最后结果如下。

这里有两张表，dept_manager和salaries的to_date都要等于9999-01-01，而且dept_manager和salaries的emp_no要相等，具体代码如下。

select salaries.emp_no, salary, salaries.from_date, salaries.to_date, dept_nofrom salaries, dept_managerwhere dept_manager.to_date='9999-01-01' and salaries.to_date='9999-01-01'and salaries.emp_no=dept_manager.emp_noorder by salaries.emp_no asc;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'order by s.emp_no

Leetcode175. 组合两个表

表1: Person+-------------+---------+| 列名         | 类型     |+-------------+---------+| PersonId    | int     || FirstName   | varchar || LastName    | varchar |+-------------+---------+PersonId 是上表主键表2: Address+-------------+---------+| 列名         | 类型    |+-------------+---------+| AddressId   | int     || PersonId    | int     || City        | varchar || State       | varchar |+-------------+---------+AddressId 是上表主键编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

在Leetcode这题有一个要求：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息。其实这就是左连接。

select FirstName,LastName,City,State from Person left join Address on Person.PersonId = Address.PersonId

查找所有员工的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`));

这个题和Leetcode中的175. 组合两个表完全一样，就是一个左连接的问题。

Leetcode176 第二高的薪水

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。+----+--------+| Id | Salary |+----+--------+| 1  | 100    || 2  | 200    || 3  | 300    |+----+--------+例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。+---------------------+| SecondHighestSalary |+---------------------+| 200                 |+---------------------+

首先考虑选取最高工资的， 然后再选取次高， 其中用到了嵌套

SELECT max(Salary) AS SecondHighestSalary FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee);

用order BY 排序，再LIMIT output，输出更加快。

SELECT max(Salary) AS SecondHighestSalary FROM Employee WHERE Salary < (SELECT SalaryFROM Employee ORDER BY Salary DESC LIMIT 1);

最好的方法就是使用ifnull + limit + offset

ifnull(expression,value)

• 当expression获得数据为空的时候，返回value，有点类似python的 dict.get(x，value)的形式，即当一个查询没有对应的值的时候，返回一个默认值，这个默认值可以自定义

limit x offset y

• 跳过y条记录，返回x条记录

order by xx

• 这个就是按照xx字段排序，后面可以用desc/asc指定是降序还是升序

• 那么，综合以上，就是要按照Salary字段排序且按降序排序，并跳过排序结果的第一条，再返回一条

• 因为是跳过了降序排序结果的第一条，再返回一条，那么返回的就是第二高的记录

• 并用ifnull函数控制查询结果为空的时候的返回结果

select ifnull((select distinct Salary from Employee order by Salary desc limit 1 offset 1),null) as SecondHighestSalary

Leetcode 177. 第N高的薪水

编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。+----+--------+| Id | Salary |+----+--------+| 1  | 100    || 2  | 200    || 3  | 300    |+----+--------+例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。+------------------------+| getNthHighestSalary(2) |+------------------------+| 200                    |+------------------------+

代码逻辑基本和上一题的一样，就是多了一个判断的条件。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN  if N<0 then        RETURN (select min(Salary) from Employee);  else       set N = N-1;    RETURN (        # Write your MySQL query statement below.        select ifnull((select distinct Salary from Employee order by Salary desc limit N,1),null) as NthHighestSalay     );  end if;END

今晚先学到这里了，有点累了。

如果你想跟博主建立亲密关系，可以关注博主，或者关注博主公众号“Python之王”，了解一个非本科程序员是如何成长的。

博主ID：润森（weixin_44510615），希望大家点赞、评论、收藏

转载地址：https://maoli.blog.csdn.net/article/details/108673115 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！