【LeetCode】数据库 - 组合两个表

【题目描述】

表1: Person

+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId 是上表主键

表2: Address

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId 是上表主键

 

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

 

FirstName, LastName, City, State

【题目解答】

首先建表、生成数据：

create table `person` (
	`personid` int (10) unsigned not null auto_increment,
	`firstname` varchar (10) not null,
	`lastname` varchar (10) not null,
	primary key (`personid`)
)engine = innodb auto_increment = 5 default charset = utf8;

create table `address` (
  `addressid` int(10) unsigned not null auto_increment,
  `personid` int(10) unsigned not null,
  `city` varchar(255) not null,
  `state` varchar(255) not null,
  primary key (`addressid`)
) engine=innodb auto_increment=3 default charset=utf8;

insert into person (firstname, lastname) value ("a1", "b1"),("a2", "b2"),("a3", "b3");
insert into address (personid, city, state) value (2, "c1", "d1"),(1, "c2", "d2");

然后分析题目，返回的信息中两张表中各占一部分，并且有[无论person是否有地址信息]这个条件的要求，所以很自然想到链接，这里用左链接来解决，左链接：获取左表左右的记录，即使右表没有与之匹配的记录。

首先写最简单的一部分，保证查出来的是4个列：

mysql> select FirstName, LastName, City, State from Person, Address;
+-----------+----------+------+-------+
| FirstName | LastName | City | State |
+-----------+----------+------+-------+
| a1        | b1       | c1   | d1    |
| a1        | b1       | c2   | d2    |
| a2        | b2       | c1   | d1    |
| a2        | b2       | c2   | d2    |
| a3        | b3       | c1   | d1    |
| a3        | b3       | c2   | d2    |
+-----------+----------+------+-------+
6 rows in set (0.00 sec)

然后使用左链接，将Person表作为左表，链接条件是两张表的PersonId：

mysql> select p.FirstName, p.LastName, a.City, a.State from Person p left join 
Address a on p.PersonId = a.PersonId;
+-----------+----------+------+-------+
| FirstName | LastName | City | State |
+-----------+----------+------+-------+
| a2        | b2       | c1   | d1    |
| a1        | b1       | c2   | d2    |
| a3        | b3       | NULL | NULL  |
+-----------+----------+------+-------+
3 rows in set (0.00 sec)