【leetcode database】组合两个表、第二高的薪水、第N高的薪水、分数排名、超过经理收入的员工、查找重复的电子邮箱、 从不订购的客户

175. 组合两个表

题目

表1: Person，PersonId 是上表主键

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

表2: Address，AddressId 是上表主键

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

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

FirstName, LastName, City, State

思路：left join

重点在这一句“无论 person 是否有地址信息”，用到左/右连接的知识

select Person.FirstName, Person.LastName, Address.City, Address.State
from Person left join Address
on Address.PersonId=Person.PersonId

176. 第二高的薪水

题目

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

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

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

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

思路：group by, order by desc, limit offset

1. 先按照薪水分组并且降序排序order by xxx desc，然后选取排在第二位的薪水值limit 1 offset 1

• limit n 其实就等于 limit 0,n 或者 limit n offset 0: 这个0表示偏移量

2. 如果没找到的话，查询会返回空值，但题意要求返回null值。所以我们利用select NULL会返回null值的特点，建立临时表

# 1、先按照薪水分组并且降序排序order by xxx desc，然后选取排在第二位的薪水值limit 1 offset 1
# limit n 其实就等于 limit 0,n 或者 limit n offset 0: 这个0表示偏移量
# 2、如果没找到的话，查询会返回空值，但题意要求返回null值。所以我们利用select NULL会返回null值的特点，建立临时表
select (select Salary as SecondHighestSalary
    from Employee
    group by Salary
    order by Salary desc
    limit 1 offset 1) as SecondHighestSalary

六种方案

6种方案诠释MySQL通用查询策略

177. 第N高的薪水

题目

在第二高的薪水上相当于多了一个输入参数

思路（同）

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N := N-1; # 记得先把n减1
  RETURN (
      # Write your MySQL query statement below.
      select Salary 
      from Employee
      group by Salary
      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    |
+-------+------+

重要提示：对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 `Rank`

思路：count(distinct)

• 先按分数降序排序，然后对于每个人，查询比他分数高的分数有多少（去重），这样就能计算出排名啦
• 注意新加的列，列名要用撇号扩起来 `Rank`

# 先按分数降序排序，然后对于每个人，查询比他分数高的分数有多少（去重），这样就能计算出排名啦
select a.Score , (select count(distinct b.Score) from Scores b where b.Score>=a.Score) as 'Rank'
from Scores a
order by a.Score desc

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, Employee b 
where a.ManagerId = b.Id and a.Salary > b.Salary

182. 查找重复的电子邮箱

题目

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

示例：

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

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

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

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

思路：group by, having

select Email
from Person
group by Email
having count(*) > 1

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

思路: 查找不在表里的数据not in

顾客id没有出现在已购订单order的顾客id中过，即查找不在表里的数据，那就用关键词not in

select a.Name as Customers
from Customers a
where a.id not in (select b.CustomerId from Orders b)