当前位置:   article > 正文

Leetcode题库(数据库详解)难度等级-简单_leetcode分级

leetcode分级

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:左外连接

  1. SELECT
  2. p.firstName,
  3. p.lastName,
  4. a.city,
  5. a.state
  6. FROM
  7. person p
  8. LEFT JOIN address a ON p.PersonId = a.PersonId

解题步骤:

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

题解2:子查询

  1. SELECT
  2. p.firstName,
  3. p.lastName,
  4. ( SELECT a.city FROM address a WHERE p.PersonId = a.PersonId ) AS city,
  5. ( SELECT a.state FROM address a WHERE p.PersonId = a.PersonId ) AS state
  6. FROM
  7. 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

  1. select (
  2. select DISTINCT salary
  3. from Employee
  4. Order by salary desc
  5. limit 1,1
  6. ) as SecondHighestSalary ;

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

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

  1. select ifnull(
  2. (select max(salary)
  3. from Employee
  4. where salary < (select max(salary) from Employee)
  5. ),null
  6. ) as SecondHighestSalary;

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

题解3:开窗函数

  1. SELECT
  2. IFNULL((
  3. SELECT
  4. salary
  5. FROM
  6. ( SELECT DISTINCT ( salary ), dense_rank ( ) over ( ORDER BY salary DESC ) AS ranking FROM Employee ) AS rankTable
  7. WHERE
  8. ranking = 2
  9. ),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:单表查询

  1. CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
  2. BEGIN
  3. SET N := N-1;
  4. RETURN (
  5. # Write your MySQL query statement below.
  6. select salary
  7. from employee
  8. GROUP BY salary
  9. ORDER BY salary DESC
  10. limit N, 1
  11. );
  12. 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:子查询

  1. CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
  2. BEGIN
  3. RETURN (
  4. # Write your MySQL query statement below.
  5. SELECT
  6. DISTINCT e.salary
  7. FROM
  8. employee e
  9. WHERE
  10. (SELECT count(DISTINCT salary) FROM employee WHERE salary>e.salary) = N-1
  11. );
  12. 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:子查询

  1. CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
  2. BEGIN
  3. RETURN (
  4. # Write your MySQL query statement below.
  5. SELECT
  6. e1.salary
  7. FROM
  8. employee e1 JOIN employee e2 ON e1.salary <= e2.salary
  9. GROUP BY
  10. e1.salary
  11. HAVING
  12. count(DISTINCT e2.salary) = N
  13. );
  14. 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:窗口函数

  1. CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
  2. BEGIN
  3. RETURN (
  4. # Write your MySQL query statement below.
  5. SELECT
  6. DISTINCT salary
  7. FROM
  8. (SELECT
  9. salary, dense_rank() over(ORDER BY salary DESC) AS rnk
  10. FROM
  11. employee) tmp
  12. WHERE rnk = N
  13. );
  14. 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函数

  1. SELECT
  2. score,
  3. dense_rank ( ) over ( ORDER BY score DESC ) AS "rank"
  4. FROM
  5. 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

  1. +-------------+---------+
  2. | Column Name | Type |
  3. +-------------+---------+
  4. | id | int |
  5. | num | varchar |
  6. +-------------+---------+
  7. id 是这个表的主键。

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

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

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

示例 1:

  1. 输入:
  2. Logs 表:
  3. +----+-----+
  4. | Id | Num |
  5. +----+-----+
  6. | 1 | 1 |
  7. | 2 | 1 |
  8. | 3 | 1 |
  9. | 4 | 2 |
  10. | 5 | 1 |
  11. | 6 | 2 |
  12. | 7 | 2 |
  13. +----+-----+
  14. 输出:
  15. Result 表:
  16. +-----------------+
  17. | ConsecutiveNums |
  18. +-----------------+
  19. | 1 |
  20. +-----------------+
  21. 解释:1 是唯一连续出现至少三次的数字。

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

  1. SELECT DISTINCT
  2. l1.Num AS ConsecutiveNums
  3. FROM
  4. Logs l1
  5. LEFT JOIN Logs l2 ON l1.id = l2.id - 1
  6. LEFT JOIN Logs l3 ON l2.id = l3.id - 1
  7. WHERE
  8. l1.Num = l2.Num
  9. AND l2.Num = l3.Num

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

题解2:自连接查询+distinct

  1. SELECT
  2. l1.Num
  3. FROM
  4. LOGS l1,
  5. LOGS l2,
  6. LOGS l3
  7. WHERE
  8. l1.Num = l2.Num
  9. AND l2.Num = l3.Num
  10. AND l1.Id = l2.Id - 1
  11. AND l2.id = l3.id - 1

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

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/792658
推荐阅读
相关标签
  

闽ICP备14008679号