• 数据库左连接 a left join b on


    左连接查询
    保留左边主表的所有行(即使在右表没有匹配的行),右表输出满足 on 条件的行,不满足的输出null
     

    示例:组合两个表 - 力扣

    表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 

    # from A
    # left join B
    # on 
    
    select A.FirstName, A.LastName, B.City, B.State
    from Person as A
    left join Address as B
    on A.PersonId=B.PersonId
    (170ms) # 本题可优化思路:Address表可能重复保存某条地址信息,因此Address表先用distinct去重
    select A.FirstName, A.LastName, B.City, B.State from Person as A left join (select distinct PersonId, City, State from Address) as B on A.PersonId=B.PersonId
    (150ms)
    输入:
    {"headers": {"Person": ["PersonId", "LastName", "FirstName"], "Address": ["AddressId", "PersonId", "City", "State"]}, "rows": {"Person": [[1, "Wang", "Allen"]], "Address": [[1, 2, "New York City", "New York"]]}}
    
    输出:
    {"headers":["FirstName","LastName","City","State"],"values":[["Allen","Wang",null,null]]}
  • 相关阅读:
    CSU 1505: 酷酷的单词【字符串】
    HDU 2036 改革春风吹满地【计算几何/叉乘求多边形面积】
    HDU 2034 人见人爱A-B【STL/set】
    HDU 2031 进制转换
    HDU 1020 Encoding【连续的计数器重置】
    HDU 1999 不可摸数【类似筛法求真因子和】
    动态规划总结
    CSU 1785: 又一道简单题
    CSU 1779: 错误的算法【矩阵/模拟】
    CSU 1777: 大还是小?【模拟/后导0】
  • 原文地址:https://www.cnblogs.com/ldy-miss/p/11837376.html
Copyright © 2020-2023  润新知