leetcode(mysql) 组合两个表

                                               组合两个表

 

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;