耿雨飞的个人博客

[[ slide_text ]]

[[ item.c ]]

面试题数据库

发布时间：2023-03-23 作者：gengyufei 来源：gengyufei个人博客

Mysql 数据结构与算法 面试集锦

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

以 任意顺序 返回结果表。

### 题解
```
select 
	FirstName, LastName, City, State
from Person 
left join Address on Person.PersonId = Address.PersonId;
```

# 中等
## 第二高的薪水
### 题目
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                |
+---------------------+
```

### 题解
方法一：使用子查询和 LIMIT 子句
算法
将不同的薪资按降序排序，然后使用 LIMIT 子句获得第二高的薪资。
```
SELECT DISTINCT
    Salary AS SecondHighestSalary
FROM
    Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
```
然而，如果没有这样的第二最高工资，这个解决方案将被判断为 “错误答案”，因为本表可能只有一项记录。为了克服这个问题，我们可以将其作为临时表。
```
SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
;
```
方法二：使用 IFNULL 和 LIMIT 子句
解决 “NULL” 问题的另一种方法是使用 “IFNULL” 函数，如下所示。
```
SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary
```

## 第N高的薪水
### 题目
表: Employee
```
+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。
```

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

查询结果格式如下所示。

示例 1:
```
输入: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
n = 2
输出: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+
```
示例 2:
```
输入: 
Employee 表:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
n = 2
输出: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null                   |
+------------------------+
```

### 题解
解题思路
先在子查询中用 dense_rank() 窗口函数，计算出排名。
然后，去重查询出排名等于 N 的薪水。
```
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
        SELECT 
            DISTINCT salary
        FROM 
            (SELECT 
                salary, dense_rank() over (ORDER BY salary DESC) AS 'rank1'
             FROM 
                employee) tmp
        WHERE tmp.rank1 = N
  );
END
```
## 分数排名
### 题目
表: 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    |
+-------+------+
```
### 题解
**方法一**：
最后的结果包含两个部分，第一部分是降序排列的分数，第二部分是每个分数对应的排名。

第一部分不难写：
```
select a.Score as Score
from Scores a
order by a.Score DESC
```
比较难的是第二部分。假设现在给你一个分数X，如何算出它的排名Rank呢？
我们可以先提取出大于等于X的所有分数集合H，将H去重后的元素个数就是X的排名。比如你考了99分，但最高的就只有99分，那么去重之后集合H里就只有99一个元素，个数为1，因此你的Rank为1。
先提取集合H：
```
select b.Score from Scores b where b.Score >= X;
```
我们要的是集合H去重之后的元素个数，因此升级为：
```
select count(distinct b.Score) from Scores b where b.Score >= X as Rank;
```
而从结果的角度来看，第二部分的Rank是对应第一部分的分数来的，所以这里的X就是上面的a.Score，把两部分结合在一起为：
```
select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a
order by a.Score DESC
```

**方法二**：
直接使用窗口函数 dense_rank
```
select 
    score,
    dense_rank() over (order by score desc) as 'rank'
from scores;
```

## 连续出现的数字
### 题目
表：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 是唯一连续出现至少三次的数字。
```
### 题解
窗口函数

lead(列名，offset, 默认值)：向后偏移 offset 的列值；
lag(列名，offset, 默认值) ：向前偏移 offset 的列值；
判断逻辑

找到当前行的上一行，当前行的下一行，如果 Num 都相等，则返回；
返回值去重。
```
SELECT DISTINCT(a) AS ConsecutiveNums
FROM (
    SELECT LEAD(Num, 1) OVER (ORDER BY Id) AS a,
            Num AS b,
            LAG(Num, 1) OVER (ORDER BY Id) AS c
    FROM Logs
) t
WHERE t.a = t.b AND t.a = t.c
```

# 困难

上一篇：面试题算法题下一篇：面试题 Linux & shell

你觉得文章怎么样

耿雨飞的个人博客

面试题 数据库

面试题数据库