美文网首页
[LeetCode] Combine Two Tables 组合

[LeetCode] Combine Two Tables 组合

作者: 空城为谁留 | 来源:发表于2018-03-16 16:45 被阅读0次

    中文题目

    表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


    英文题目

    Table: Person

    +-------------+---------+
    | Column Name | Type |
    +-------------+---------+
    | PersonId | int |
    | FirstName | varchar |
    | LastName | varchar |
    +-------------+---------+
    PersonId is the primary key column for this table.
    Table: Address

    +-------------+---------+
    | Column Name | Type |
    +-------------+---------+
    | AddressId | int |
    | PersonId | int |
    | City | varchar |
    | State | varchar |
    +-------------+---------+
    AddressId is the primary key column for this table.

    Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

    FirstName, LastName, City, State


    参考答案

    方法一:

    SELECT p.FirstName, p.LastName, a.City, a.State FROM Person2 p LEFT JOIN Address a USING (PersonId)
    

    方法二:

    SELECT p.FirstName, p.LastName, a.City, a.State FROM Person2 p LEFT JOIN Address a ON(p.PersonId = a.PersonId)
    

    方法三:

    SELECT p.FirstName, p.LastName, a.City, a.State FROM Person2 p,Address a WHERE p.PersonId = a.PersonId
    

    相关文章

      网友评论

          本文标题:[LeetCode] Combine Two Tables 组合

          本文链接:https://www.haomeiwen.com/subject/qnkrqftx.html