左关联查询 col 每一个 cit http sid combine sql 查询
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:
有两个数据表:Person 表和 Address 表。Person(人员)表主键为 PersonId,Address(地址)表主键是 AddressId,通过 PersonId 与 Person 表关联。
编写一个 SQL 查询,对于 Person 表中的每一个人,取出 FirstName, LastName, City, State 属性,无论其地址信息是否存在。
解题思路:
本题是一个简单的连表查询,以 Person 表为主表,Address 表为副表。所以可以使用左关联查询来进行联表查询。
关于左 (外) 关联、右 (外) 关联、内关联以及外关联查询。详见此篇博客:http://www.cnblogs.com/afirefly/archive/2010/10/08/1845906.html
一句话介绍就是:左关联(Left Join)就是以左边的表为准,只要左边的表有数据就输出,不管右边的表有没有数据。右关联(Right Join)相反,以右边的表为准。而内关联则是两张表都要有数据才能查询到。外关联则是只要任意一张表有数据就能查询并显示。
所以本题使用左关联。
SQL 语句:
SELECT p.FirstName, p.LastName, a.City, a.StateFROM Person p LEFT JOIN Address a on a.PersonId=p.PersonId;
【LeetCode 刷题】SQL-Combine Two Tables
来源: http://www.bubuko.com/infodetail-2128876.html