175. 组合两个表
175. 组合两个表
题目
Table: Person
+-------------+---------+ | Column Name | Type | +-------------+---------+ | personId | int | | lastName | varchar | | firstName | varchar | +-------------+---------+
personId is the primary key (column with unique values) for this table.
This table contains information about the ID of some persons and their first and last names.
Table: Address
+-------------+---------+ | Column Name | Type | +-------------+---------+ | addressId | int | | personId | int | | city | varchar | | state | varchar | +-------------+---------+
addressId is the primary key (column with unique values) for this table.
Each row of this table contains information about the city and state of one person with ID = PersonId.
Write a solution to report the first name, last name, city, and state of each person in the Person
table. If the address of a personId
is not present in the Address
table, report null
instead.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input:
Person table:
+----------+----------+-----------+ | personId | lastName | firstName | +----------+----------+-----------+ | 1 | Wang | Allen | | 2 | Alice | Bob | +----------+----------+-----------+
Address table:
+-----------+----------+---------------+------------+ | addressId | personId | city | state | +-----------+----------+---------------+------------+ | 1 | 2 | New York City | New York | | 2 | 3 | Leetcode | California | +-----------+----------+---------------+------------+
Output:
+-----------+----------+---------------+----------+ | firstName | lastName | city | state | +-----------+----------+---------------+----------+ | Allen | Wang | Null | Null | | Bob | Alice | New York City | New York | +-----------+----------+---------------+----------+
Explanation:
There is no address in the address table for the personId = 1 so we return null in their city and state.
addressId = 1 contains information about the address of personId = 2.
题目大意
表: Person
+-------------+---------+ | Column Name | Type | +-------------+---------+ | personId | int | | lastName | varchar | | firstName | varchar | +-------------+---------+
personId 是该表的主键(具有唯一值的列)。
该表包含一些人的 ID 和他们的姓和名的信息。
表: Address
+-------------+---------+ | Column Name | Type | +-------------+---------+ | addressId | int | | personId | int | | city | varchar | | state | varchar | +-------------+---------+
addressId 是该表的主键(具有唯一值的列)。
该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。
编写解决方案,报告 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 的地址信息。
解题思路
- 数据库中有两张表:
Person
表存储个人信息,Address
表存储地址信息。两表通过personId
关联。- 从
Person
表中选择firstName
和lastName
。 - 从
Address
表中选择city
和state
。
- 从
- 由于需要包括
Person
表中的所有记录,即使对应的Address
为空,也要显示该人的信息,因此选择LEFT JOIN
。LEFT JOIN
会保留左表 (Person
) 的所有记录,对于没有匹配右表 (Address
) 的记录,右表的字段显示为NULL
。
- 利用
ON
子句连接两表,匹配条件是p.personId = a.personId
。
复杂度分析
- 时间复杂度:
O(n + m)
,其中n
是Person
表的行数,m
是Address
表的行数,连接操作需要扫描两张表。 - 空间复杂度:
O(n + m)
,需要存储连接结果。
代码
SELECT p.firstName, p.lastName, a.city, a.state
FROM Person p
LEFT JOIN Address a
ON p.personId = a.personId
相关题目
题号 | 标题 | 题解 | 标签 | 难度 | 力扣 |
---|---|---|---|---|---|
577 | 员工奖金 | 数据库 | 🟢 | 🀄️ 🔗 |