LeetCode数据库刷题笔记(一)

LeetCode数据库刷题笔记(一)

---

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/

注：以下答案仅供参考，如果有更好的方法可以留言交流

---

175. 组合两张表

题目:

表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

答案：

SELECT 
    a.FirstName, a.LastName, b.City, b.State 
FROM  
    Person a
LEFT JOIN
    Address b 
ON a.personid = b.personid

左向外联接的结果集，包括LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

---

176.第二高薪水

题目：

编写一个 SQL 查询，获取 Employee表中第二高的薪水（Salary）。

Id	Salary
1	100
2	200
3	300

例如上述Employee表，SQL查询应该返回200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

SecondHighestSalary
200

答案：

SELECT
(
    SELECT
        distinct(salary) 
    FROM
        Employee 
    ORDER BY 
        salary desc 
    LIMIT 
        1,1
)AS 
    SecondHighestSalary

使用LIMIT来输出排序后的结果,例如查询第三高的工资可以输出LIMIT 1,2 (或者LIMIT 1 OFFSET 2),外层嵌套一层SELECT是因为题目要求返回NULL

---

177.第N高的薪水

题目：

编写一个 SQL 查询，获取 Employee表中第n高的薪水（Salary）。

Id	Salary
1	100
2	200
3	300

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

SecondHighestSalary
200

答案：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT (SELECT distinct(Salary) from Employee order by Salary desc limit 1 OFFSET N )
  );
END

---

178.分数排名

题目：

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

Id	Score
1	3.50
2	3.65
3	4.00
4	3.85
5	4.00
6	3.65

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

Score	Rank
4.00	1
4.00	1
3.85	2
3.65	3
3.65	3
3.50	4

答案：

SELECT 
  a.Score, 
  b.Rank 
FROM 
  SCORES a 
LEFT JOIN 
  (
    SELECT 
      @Rank:=@Rank + 1  Rank,
      c.Score 
    FROM 
    (
      SELECT DISTINCT 
        Score 
      FROM 
        SCORES 
      ORDER BY 
        Score  DESC
    ) c,(SELECT @Rank := 0) d
  ) b 
On 
  a.Score = b.Score
ORDER BY 
  a.Score DESC

首先是最内层语句SELECT DISTINCT Score From SCORES ORDER BY Score DESC查询出除重后所有成绩，对于示例表输出如下

Score
4.0
3.85
3.65
3.50

然后使用 @Rank:=@Rank + 1 Rank 构造自增列作为Rank

自增语法的使用示例为：

SELECT 
  @Rank:=@Rank + 1 Rank,
  b.* 
FROM 
  (SELECT @Rank := 0) a,
  XXXTable b

查询出的表为

Score	Rank
4.0	1
3.85	2
3.65	3
3.50	4

最后将原表与上表进行左连接即可得到题目要求的表

---

180.连续出现的数字

题目:

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

Id	Num
1	1
2	1
3	1
4	2
5	1
6	2
7	2

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

ConsecutiveNums
1

答案：

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num

官方解法

---

181.超过经理收入的员工

题目：

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

Id	Name	Salary	ManagerId
1	Joe	70000	3
2	Henry	80000	4
3	Sam	60000	NULL
4	Max	90000	NULL

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

Employee
Joe

答案：

SELECT 
    a.Name as Employee  
From 
    Employee a 
LEFT JOIN 
    Employee b 
ON 
    a.ManagerId = b.Id 
WHERE 
    a.Salary > b.Salary