Leetcode题解-数据库

175. 组合两个表

题目描述

表1: Person

+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId 是上表主键

表2: Address

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供?person 的以下信息：

1	FirstName, LastName, City, State

解题思路

1
2
3

select p.FirstName, p.LastName, a.City, a.State
from Person as p left join Address as a
on p.PersonId = a.PersonId;

176. 第二高的薪水

题目描述

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

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

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

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

解题思路

直接select无法输出第二高薪水为空的情况。

1	select distinct salary as SecondHighestSalary from Employee order by salary desc limit 1, 1;

在外面加一层select，可以将空的赋值给SecondHighestSalary。

1	select(select distinct salary from Employee order by salary desc limit 1, 1) as SecondHighestSalary;

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 INT
BEGIN
    SET N = N-1;
  RETURN (
      # Write your MySQL query statement below.
      select distinct ifnull(salary, null) from Employee order by salary desc limit N, 1
  );
  
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    |
+-------+------+

解题思路

# Write your MySQL query statement below
select p1.Score as Score,
(select Count(distinct p2.score) from Scores as p2 where p2.score >= p1.score) as Rank
from Scores as p1
order by Score desc;

180. 连续出现的数字

题目描述

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

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

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

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

解题思路

# Write your MySQL query statement below
select distinct l1.Num as ConsecutiveNums
from Logs l1, Logs l2, Logs l3
where l1.num = l2.num and l2.num = l3.Num and l2.Id = l1.Id+1 and l3.Id = l2.id+1

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      |
+----------+

解题思路

# Write your MySQL query statement below
select e1.Name as Employee 
from Employee e1, Employee e2
where e1.ManagerId = e2.Id and e1.salary > e2.salary

182. 查找重复的电子邮箱

题目描述

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

根据以上输入，你的查询应返回以下结果：

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

说明：所有电子邮箱都是小写字母。

解题思路

1
2
3

# Write your MySQL query statement below
select Email from Person 
group by Email having count(ID)>1

1
2
3

# Write your MySQL query statement below
select distinct p1.Email from Person p1, Person p2 
where p1.Email = p2.Email and p1.Id != p2.Id

183. 从不订购的客户

题目描述

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders 表：

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

例如给定上述表格，你的查询应返回：

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

解题思路

# Write your MySQL query statement below
select c.Name as Customers 
from Customers c left join Orders o on c.Id = o.CustomerId 
where o.id is null

1
2
3

# Write your MySQL query statement below
select Name as Customers from Customers 
where Id not in (select CustomerId from Orders)

184. 部门工资最高的员工

题目描述

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

解题思路

# Write your MySQL query statement below
select d.Name as Department, e.Name as Employee, e.Salary
from Employee as e,
Department as d,
(select DepartmentId, max(Salary) as max from Employee group by DepartmentId) as m
where e.DepartmentId = m.DepartmentId
and e.Salary = m.max
and e.DepartmentId = d.Id