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