组合两个表
1、题目:
表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
2、解题步骤:
(1) 创建表:
CREATE TABLE `person` (
`PersonId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'PersonId',
`FirstName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名',
`LastName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓',
PRIMARY KEY (`PersonId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
CREATE TABLE `address` (
`AddressId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`PersonId` int(11) NOT NULL COMMENT 'Person表主键ID',
`City` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '城市',
`State` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '国家',
PRIMARY KEY (`AddressId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
(2) 插入数据:
INSERT INTO `person` VALUES (1, 'xuejin', 'cao');
INSERT INTO `person` VALUES (2, '麻花', '重庆');
(3) 查询SQL
a、方案一:
SELECT p.FirstName, p.LastName, a.City, a.State
FROM person p
LEFT JOIN address a ON a.PersonId = p.PersonId;
b、方案二:数据量很大的时候建议使用该方案
SELECT p.FirstName, p.LastName, a.City, a.State
FROM person p
LEFT JOIN (
SELECT PersonId, City, State
FROM address
) a
ON a.PersonId = p.PersonId;