LeetCode数据库—组合两个表

SQL架构

Create table Person (PersonId int, FirstName varchar(255), LastName varchar(255));
Create table Address (AddressId int, PersonId int, City varchar(255), State varchar(255));
Truncate table Person;
insert into Person (PersonId, LastName, FirstName) values ('1', 'Wang', 'Allen');
Truncate table Address;
insert into Address (AddressId, PersonId, City, State) values ('1', '2', 'New York City', 'New York');

查看表记录

mysql> select * from person;
+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Allen     | Wang     |
+----------+-----------+----------+
1 row in set (0.00 sec)
mysql> select * from address;
+-----------+----------+---------------+----------+
| AddressId | PersonId | City          | State    |
+-----------+----------+---------------+----------+
|         1 |        2 | New York City | New York |
+-----------+----------+---------------+----------+
1 row in set (0.00 sec)

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

mysql> select p.FirstName,p.LastName,d.City,d.State from
    ->  person p left join address d
    ->  on p.PersonId=d.PersonId;
+-----------+----------+------+-------+
| FirstName | LastName | City | State |
+-----------+----------+------+-------+
| Allen     | Wang     | NULL | NULL  |
+-----------+----------+------+-------+
1 row in set (0.00 sec)

LeetCode数据库—组合两个表

推荐阅读更多精彩内容