【SQL每日练习 难度简单】题源：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 。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例 1:

输入:
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 的地址信息。

相关知识点

内连接与外连接
INNER JOIN表示：表中存在至少一个匹配时返回行，A,B表值都存在情况
LEFT JOIN表示：从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
RIGHT JOIN 表示：从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

解题思路

由于题目中提到“如果 personId 的地址不在 Address 表中，则报告为空 null 。”，说明右表中可能没有匹配，则用LEFT JOIN连接两个表。

代码

select Person.firstName,Person.lastName,Address.city,Address.state
from Person left join Address
on Person.personId = Address.personId

---

LeetCode 181. 超过经理收入的员工

表：Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| salary      | int     |
| managerId   | int     |
+-------------+---------+

Id是该表的主键。
该表的每一行都表示雇员的ID、姓名、工资和经理的ID。

编写一个SQL查询来查找收入比经理高的员工。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例 1:

输入:
Employee 表:

+----+-------+--------+-----------+
| id | name  | salary | managerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | Null      |
| 4  | Max   | 90000  | Null      |
+----+-------+--------+-----------+

输出:

+----------+
| Employee |
+----------+
| Joe      |
+----------+

解释: Joe 是唯一挣得比经理多的雇员。

相关知识点

自连接
使用自连接可以将自身表的一个镜像当作另一个表来对待，即复制自身得到两个表

解题思路

首先managerId非空则表示是雇员，managerId为空则表示为经理。
那办法就是先找到雇员对应的经理，再比较他们的工资

代码

法一使用where

select a.Name as 'Employee'
from Employee as a, Employee as b
where a.managerId = b.id and a.salary > b.salary

法二使用join

select a.Name as 'Employee'
from Employee as a join Employee as b
on a.managerId = b.id 
where a.salary > b.salary

---

LeetCode182. 查找重复的电子邮箱

表: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+

id 是该表的主键列。
此表的每一行都包含一封电子邮件。电子邮件不包含大写字母。

编写一个 SQL 查询来报告所有重复的电子邮件。 请注意，可以保证电子邮件字段不为 NULL。

以 任意顺序 返回结果表。

查询结果格式如下例。

示例 1:

输入:
Person 表:

+----+---------+
| id | email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

输出:

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

解释: a@b.com 出现了两次。

相关知识点

GROUP BY 和 HAVING
GROUP BY 一般配合聚合函数使用，对于分组后每个分组都会返回该组的一个结果
HAVING 对分组之后的结果进行了一个筛选

解题思路

这道题目相当于去重或者是统计个数，将email分组，每组中的记录的个数大于1说明是重复的记录。

代码

select email
from Person
group by email
having count(email)>1

---

LeetCode183. 从不订购的客户

某网站包含两个表，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
不在表中的数据，但尽量避免使用，看个例子

select 1 from dual where 1 not in(2, null)

1!=null的结果是不确定，true 和 不确定进行与运算的结果非true，所以上述语句的没有输出结果。
就是说not in后面跟的范围中如果有null值，则结果为不确定

解题思路

使用not in将有订购记录的用户踢出去。

代码

select Customers.name as Customers
from Customers
where Customers.id not in (
    select CustomerId
    from Orders
)

LeetCode196. 删除重复的电子邮箱

表: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+

id是该表的主键列。
该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。

编写一个 SQL 删除语句来 删除 所有重复的电子邮件，只保留一个id最小的唯一电子邮件。

以 任意顺序 返回结果表。 （注意： 仅需要写删除语句，将自动对剩余结果进行查询）

查询结果格式如下所示。

示例 1:

输入:
Person 表:

+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+

输出:

+----+------------------+
| id | email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

解释: john@example.com重复两次。我们保留最小的Id = 1。

解题思路

用两张person表，找到具有相同电子邮件地址的更大 ID，delete删除即可。

代码

DELETE p1
from Person p1,Person p2
where p1.email = p2.email and p1.id > p2.id