SQL实现LeetCode(175.联合两表)

[LeetCode] 175.Combine Two Tables 联合两表

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

LeetCode还出了是来到数据库的题，来那么也来做做吧，这道题是第一道，相对来说比较简单，是一道两表联合查找的问题，我们需要用到Join操作，关于一些Join操作可以看我之前的博客SQL Left Join, Right Join, Inner Join, and Natural Join 各种Join小结，最直接的方法就是用Left Join来做，根据PersonId这项来把两个表联合起来：

解法一：

SELECT Person.FirstName, Person.LastName, Address.City, Address.State FROM Person LEFT JOIN Address ON Person.PersonId = Address.PersonId;

在使用Left Join时，我们也可以使用关键Using来声明我们相用哪个列名来进行联合：

解法二：

SELECT Person.FirstName, Person.LastName, Address.City, Address.State FROM Person LEFT JOIN Address USING(PersonId);

或者我们可以加上Natural关键字，这样我们就不用声明具体的列，MySQL可以自行搜索相同的列：

解法三：

SELECT Person.FirstName, Person.LastName, Address.City, Address.State FROM Person NATURAL LEFT JOIN Address;

参考资料：

https://leetcode.com/discuss/21216/its-a-simple-question-of-left-join-my-solution-attached

https://leetcode.com/discuss/53001/comparative-solution-between-left-using-natural-left-join

到此这篇关于SQL实现LeetCode(175.联合两表)的文章就介绍到这了,更多相关SQL实现联合两表内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • C++实现LeetCode(167.两数之和之二 - 输入数组有序)

    [LeetCode] 167.Two Sum II - Input array is sorted 两数之和之二 - 输入数组有序 Given an array of integers that is already sorted in ascending order, find two numbers such that they add up to a specific target number. The function twoSum should return indices of t

  • C++实现LeetCode(170.两数之和之三 - 数据结构设计)

    [LeetCode] 170. Two Sum III - Data structure design 两数之和之三 - 数据结构设计 Design and implement a TwoSum class. It should support the following operations: add and find. add - Add the number to an internal data structure. find - Find if there exists any pai

  • C++实现LeetCode(171.求Excel表列序号)

    [LeetCode] 171.Excel Sheet Column Number 求Excel表列序号 Related to question Excel Sheet Column Title Given a column title as appear in an Excel sheet, return its corresponding column number. For example:     A -> 1 B -> 2 C -> 3 ... Z -> 26 AA -&g

  • C++实现LeetCode(169.求大多数)

    [LeetCode] 169. Majority Element 求大多数 Given an array nums of size n, return the majority element. The majority element is the element that appears more than ⌊n / 2⌋ times. You may assume that the majority element always exists in the array. Example 1

  • C++实现LeetCode(168.求Excel表列名称)

    [LeetCode] 168.Excel Sheet Column Title 求Excel表列名称 Given a positive integer, return its corresponding column title as appear in an Excel sheet. For example:     1 -> A 2 -> B 3 -> C ... 26 -> Z 27 -> AA 28 -> AB ... Example 1: Input: 1 O

  • C++实现LeetCode(173.二叉搜索树迭代器)

    [LeetCode] 173.Binary Search Tree Iterator 二叉搜索树迭代器 Implement an iterator over a binary search tree (BST). Your iterator will be initialized with the root node of a BST. Calling next() will return the next smallest number in the BST. Note: next() and

  • C++实现LeetCode(172.求阶乘末尾零的个数)

    [LeetCode] 172. Factorial Trailing Zeroes 求阶乘末尾零的个数 Given an integer n, return the number of trailing zeroes in n!. Example 1: Input: 3 Output: 0 Explanation: 3! = 6, no trailing zero. Example 2: Input: 5 Output: 1 Explanation: 5! = 120, one trailing

  • C++实现LeetCode(166.分数转循环小数)

    [LeetCode] 166.Fraction to Recurring Decimal 分数转循环小数 Given two integers representing the numerator and denominator of a fraction, return the fraction in string format. If the fractional part is repeating, enclose the repeating part in parentheses. Fo

  • SQL实现LeetCode(175.联合两表)

    [LeetCode] 175.Combine Two Tables 联合两表 Table: Person +-------------+---------+ | Column Name | Type    | +-------------+---------+ | PersonId    | int     | | FirstName   | varchar | | LastName    | varchar | +-------------+---------+ PersonId is the

  • SQL实现LeetCode(184.系里最高薪水)

    [LeetCode] 184.Department Highest Salary 系里最高薪水 The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id. +----+-------+--------+--------------+ | Id | Name  | Salary | DepartmentId

  • 基于SQL Server中如何比较两个表的各组数据 图解说明

    基于SQL Server中如何比较两个表的各组数据 图解说明

    开始 前一阵子,在项目中碰到这样一个SQL查询需求,有两个相同结构的表(table_left & table_right),如下: 图1. 检查表table_left的各组(groupId),是否在表table_right中存在有一组(groupId)数据(data)与它的数据(data)完全相等. 如图1. 可以看出表table_left和table_right存在两组数据完整相等: 图2. 分析 从上面的两个表,可以知道它们存放的是一组一组的数据:那么,接下来我借助数学集合的列举法和运算进行

  • SQL实现LeetCode(183.从未下单订购的顾客)

    [LeetCode] 183.Customers Who Never Order 从未下单订购的顾客 Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything. Table: Customers. +----+-------+ | Id | Name  |

  • SQL实现LeetCode(178.分数排行)

    [LeetCode] 178.Rank Scores 分数排行 Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, th

  • SQL实现LeetCode(176.第二高薪水)

    [LeetCode] 176.Second Highest Salary 第二高薪水 Write a SQL query to get the second highest salary from the Employee table. +----+--------+ | Id | Salary | +----+--------+ | 1  | 100    | | 2  | 200    | | 3  | 300    | +----+--------+ For example, given

  • SQL实现LeetCode(180.连续的数字)

    [LeetCode] 180.Consecutive Numbers 连续的数字 Write a SQL query to find all numbers that appear at least three times consecutively. +----+-----+ | Id | Num | +----+-----+ | 1  |  1  | | 2  |  1  | | 3  |  1  | | 4  |  2  | | 5  |  1  | | 6  |  2  | | 7  |

  • SQL实现LeetCode(185.系里前三高薪水)

    [LeetCode] 185.Department Top Three Salaries 系里前三高薪水 The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id. +----+-------+--------+--------------+ | Id | Name  | Salary | DepartmentId | +--

  • SQL实现LeetCode(182.重复的邮箱)

    [LeetCode] 182.Duplicate Emails 重复的邮箱 Write a SQL query to find all duplicate emails in a table named Person. +----+---------+ | Id | Email   | +----+---------+ | 1  | a@b.com | | 2  | c@d.com | | 3  | a@b.com | +----+---------+ For example, your que

  • SQL实现LeetCode(181.员工挣得比经理多)

    [LeetCode] 181.Employees Earning More Than Their Managers 员工挣得比经理多 The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id. +----+-------+--------+-----------+ | Id | Na

随机推荐