Leetcode题库（数据库详解）难度等级-简单

鮀城小帅

已于 2022-11-13 14:05:23 修改

阅读量772

点赞数

分类专栏：设计模式与算法数据库文章标签：数据库 java 开发语言

于 2022-11-01 10:57:58 首次发布

本文链接：https://blog.csdn.net/weixin_42405670/article/details/127629384

版权

数据库同时被 2 个专栏收录

62 篇文章 17 订阅

订阅专栏

设计模式与算法

31 篇文章 3 订阅

订阅专栏

175.组合两个表

表: Person

+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+

personId 是该表的主键列。
该表包含一些人的 ID 和他们的姓和名的信息。

表: Address

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+

addressId 是该表的主键列。
该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。

编写一个SQL查询来报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中，则报告为空 null 。

以任意顺序返回结果表。

查询结果格式如下所示。

输入:
Person表:
+----------+----------+-----------+
| personId | lastName | firstName |
+----------+----------+-----------+
| 1        | Wang     | Allen     |
| 2        | Alice    | Bob       |
+----------+----------+-----------+
Address表:
+-----------+----------+---------------+------------+
| addressId | personId | city          | state      |
+-----------+----------+---------------+------------+
| 1         | 2        | New York City | New York   |
| 2         | 3        | Leetcode      | California |
+-----------+----------+---------------+------------+
输出:
+-----------+----------+---------------+----------+
| firstName | lastName | city          | state    |
+-----------+----------+---------------+----------+
| Allen     | Wang     | Null          | Null     |
| Bob       | Alice    | New York City | New York |
+-----------+----------+---------------+----------+
解释:
地址表中没有 personId = 1 的地址，所以它们的城市和州返回 null。
addressId = 1 包含了 personId = 2 的地址信息。

题解1：左外连接

SELECT
	p.firstName,
	p.lastName,
	a.city,
	a.state 
FROM
	person p
	LEFT JOIN address a ON p.PersonId = a.PersonId

解题步骤：

用左外连接，以左表为准，右表不符合条件默认会返回null。比如：PersonId=2的city、state字段都是空的

题解2：子查询

SELECT
	p.firstName,
	p.lastName,
	( SELECT a.city FROM address a WHERE p.PersonId = a.PersonId ) AS city,
	( SELECT a.state FROM address a WHERE p.PersonId = a.PersonId ) AS state 
FROM
	person p

最佳选择：左外连接，因为子查询会多次遍历数据，而连接查询只遍历一次。

176. 第二高的薪水

Employee 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
id 是这个表的主键。
表的每一行包含员工的工资信息。

编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水。如果不存在第二高的薪水，查询应该返回 null 。

查询结果如下例所示。

示例 1：

输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1 | 100    |
| 2 | 200    |
| 3 | 300    |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

示例 2：

输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| null |
+---------------------+

题解1：分页函数+IFNULL

select (
    select DISTINCT salary 
    from Employee 
    Order by salary desc
    limit 1,1
) as SecondHighestSalary ;

根据sql语句的执行顺序，distinct的执行顺序优先于order by，因此distinct对select的字段去重后，order by只能在distinct后返回的结果集进行排序。再limit进行分页查询，就是取第2页的第一行数据。第一个1:第二行(默认第一行为0),第二个1:获取行数。

题解2：max聚合+过滤最大值

select ifnull(
    (select max(salary) 
    from Employee
    where salary < (select max(salary) from Employee)
    ),null
) as SecondHighestSalary;

where子查询语句中，max聚合获取最大值，再过滤最大值；再select max(salary)此时是在过滤了最大值的结果集上做max聚合，拿到的就是第二大的。

题解3：开窗函数

SELECT
	IFNULL((
	SELECT
		salary 
	FROM
		( SELECT DISTINCT ( salary ), dense_rank ( ) over ( ORDER BY salary DESC ) AS ranking FROM Employee ) AS rankTable 
	WHERE
	ranking = 2 
	),null) AS SecondHighestSalary;

DISTINCT将salary字段去重，在此基础上，dense_rank() over()：处理连续排序，如果有两个第一级时，接下来仍然是第二级。1，1，2，3，4。这里经过去重，每一级只有一个，再按照降序排列，也就是第一级是最大的，第二级是第二大，以此类推。

然后再根据别名查询结果集，在外层的where子句中， ranking=2查询到的就是第二级，也就是salary第二大的数据。

开窗函数查询的结果如果不存在不返回NULL，而是N/A，所以要加IFNULL（）将N/A的结果返回为NULL，不然会执行失败。

177. 第N高的薪水

表: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。

编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询应该报告为 null 。

查询结果格式如下所示。

示例 1:

+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。

示例 2:

+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。

题解1：单表查询

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
  RETURN (
      # Write your MySQL query statement below.
        select salary 
        from employee
        GROUP BY salary
        ORDER BY salary DESC
        limit N, 1
  );
END

同薪同名且不跳级的问题，解决办法是用group by按薪水分组后再order by
排名第N高意味着要跳过N-1个薪水，由于无法直接用limit N-1，所以需先在函数开头处理N为N=N-1。
注：这里不能直接用limit N-1是因为limit和offset字段后面只接受正整数（意味着0、负数、小数都不行）或者单一变量（意味着不能用表达式），也就是说想取一条，limit 2-1、limit 1.1这类的写法都是报错的。

题解2：子查询

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
          DISTINCT e.salary
      FROM 
          employee e
      WHERE 
          (SELECT count(DISTINCT salary) FROM employee WHERE salary>e.salary) = N-1
  );
END

排名第N的薪水意味着该表中存在N-1个比其更高的薪水，假如N为2，那就有至少1个级别比他高；比如：1=100,2=100，3=200；那N=2，就N-1=2-1=1。
注意这里的N-1个更高的薪水是指去重后的N-1个，实际对应人数可能不止N-1个，比如1=100，2=100。
最后返回的薪水也应该去重，因为可能不止一个薪水排名第N，这里的逻辑是count()的数量去重后等于前面几级的数量，也就是N=2，那么count=1，此时salary是最大的，那么e.salary是次一级就正好符合条件；
由于对于每个薪水的where条件都要执行一遍子查询，所以当salary为第二级时，count=2，e.salary为第三极，此时的count!=2-1，所以排除。最终得到唯一一个满足条件的结果。但，这样where子句的多次执行会导致效率低下。

题解3：子查询

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
          e1.salary
      FROM 
          employee e1 JOIN employee e2 ON e1.salary <= e2.salary
      GROUP BY 
          e1.salary
      HAVING 
          count(DISTINCT e2.salary) = N
  );
END

一般来说，能用子查询解决的问题也能用连接解决。具体到本题：

    两表自连接，连接条件设定为表1的salary小于表2的salary；
    以表1的salary分组，统计表1中每个salary分组后对应表2中salary唯一值个数，即去重；
    限定步骤2中having 计数个数为N-1，即实现了该分组中表1salary排名为第N个；
    考虑N=1的特殊情形(特殊是因为N-1=0，计数要求为0)，此时不存在满足条件的记录数，但仍需返回结果，所以连接用left join；
    如果仅查询薪水这一项值，那么不用left join当然也是可以的，只需把连接条件放宽至小于等于、同时查询个数设置为N即可。因为连接条件含等号，所以一定不为空，用join即可。

题解4：窗口函数

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
        SELECT 
            DISTINCT salary
        FROM 
            (SELECT 
                salary, dense_rank() over(ORDER BY salary DESC) AS rnk
             FROM 
                employee) tmp
        WHERE rnk = N
  );
END

在mysql8.0中有相关的内置函数，而且考虑了各种排名问题：

    row_number(): 同薪不同名，相当于行号，例如3000、2000、2000、1000排名后为1、2、3、4
    rank(): 同薪同名，有跳级，例如3000、2000、2000、1000排名后为1、2、2、4
    dense_rank(): 同薪同名，无跳级，例如3000、2000、2000、1000排名后为1、2、2、3
    ntile(): 分桶排名，即首先按桶的个数分出第一二三桶，然后各桶内从1排名，实际不是很常用

显然，本题是要用第三个函数。
另外这三个函数必须要要与其搭档over()配套使用，over()中的参数常见的有两个，分别是

partition by，按某字段切分
order by，与常规order by用法一致，也区分ASC(默认)和DESC，因为排名总得有个依据

注：下面代码仅在mysql8.0以上版本可用，最新OJ已支持。

178. 分数排名

表: Scores

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| score       | decimal |
+-------------+---------+
Id是该表的主键。
该表的每一行都包含了一场比赛的分数。Score是一个有两位小数点的浮点值。

编写 SQL 查询对分数进行排序。排名按以下规则计算:

    分数应按从高到低排列。
    如果两个分数相等，那么两个分数的排名应该相同。
    在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

按 score 降序返回结果表。

查询结果格式如下所示。

示例 1:

输入:
Scores 表:
+----+-------+
| id | score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
输出:
+-------+------+
| score | rank |
+-------+------+
| 4.00 | 1    |
| 4.00 | 1    |
| 3.85 | 2    |
| 3.65 | 3    |
| 3.65 | 3    |
| 3.50 | 4    |
+-------+------+

题解1：rank函数

SELECT
	score,
	dense_rank ( ) over ( ORDER BY score DESC ) AS "rank" 
FROM
	scores

将查询到的数据根据顺序排号，相同分数的序号相同，序号不间断，1 1 2 3 3 4这种
利用 dense_rank() over函数可以实现
与另外两个计算序号的函数的对比
1.rank() over：排名相同的两名是并列，但是占两个名次，1 1 3 4 4 6这种

2.dense_rank() over：排名相同的两名是并列，共占一个名词，1 1 2 3 3 4这种

3.row_number() over这个函数不需要考虑是否并列，哪怕根据条件查询出来的数值相同也会进行连续排名 1 2 3 4 5

180. 连续出现的数字

表：Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id 是这个表的主键。

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

返回的结果表中的数据可以按任意顺序排列。

查询结果格式如下面的例子所示：

示例 1:

输入：
Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
输出：
Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
解释：1 是唯一连续出现至少三次的数字。

题解1：左外连接+where字句

SELECT DISTINCT
	l1.Num AS ConsecutiveNums 
FROM
	Logs l1
	LEFT JOIN Logs l2 ON l1.id = l2.id - 1
	LEFT JOIN Logs l3 ON l2.id = l3.id - 1 
WHERE
	l1.Num = l2.Num 
	AND l2.Num = l3.Num

左外连接关联查询三个连续的id，where字句过滤出num相同的连续id。

题解2：自连接查询+distinct

SELECT
	l1.Num 
FROM
	LOGS l1,
	LOGS l2,
	LOGS l3 
WHERE
	l1.Num = l2.Num 
	AND l2.Num = l3.Num 
	AND l1.Id = l2.Id - 1 
	AND l2.id = l3.id - 1

使用自连接查询只需要检索到哪个Num连续出现三次即可，如果连续出现两次Num说明Num连续出现的次数大于4为了避免相同的Num重复出现，使用ditinct去除相同的Num即可得解。

鮀城小帅

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Leetcode题库（数据库详解）难度等级-简单

如果 personId 的地址不在 Address 表中，则报告为空 null。用左外连接，以左表为准，右表不符合条件默认会返回null。比如：PersonId=2的city、state字段都是空的。地址表中没有 personId = 1 的地址，所以它们的城市和州返回 null。该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。该表包含一些人的 ID 和他们的姓和名的信息。addressId 是该表的主键列。personId 是该表的主键列。表: Address。
复制链接

扫一扫

专栏目录