【Leetcode】175. Combine Two Tables

難度：Easy

Person表格屬性
Column Name	Type
personId	int
lastName	varchar
firstName	varchar

personId is the primary key (column with unique values) for this table.

This table contains information about the ID of some persons and their first and last names.

Address表格屬性
Column Name	Type
addressId	int
personId	int
city	varchar
state	varchar

addressId is the primary key (column with unique values) for this table.

Each row of this table contains information about the city and state of one person with ID = PersonId

原文：

Write a solution to report the first name, last name, city, and state of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.

Return the result table in any order.

The result format is in the following example.

個人翻譯（非逐字翻譯）：

編寫一個解決方案，來報告Person表格中，每個人的名字、姓氏、城市和州。如果表格中沒有PersonID位址，則報告為Null。

輸入：

Person table:

personId	lastName	firstName
1	Wang	Allen
2	Alice	Bob

Address table:

addressId	personId	city	state
1	2	New York City	New York
2	3	Leetcode	California

輸出：

firstName	lastName	city	state
Allen	Wang	Null	Null
Bob	Alice	New York City	New York

註釋：

There is no address in the address table for the personId = 1 so we return null in their city and state.

addressId = 1 contains information about the address of personId = 2.

個人理解

典型的Left Join案例，如果寫on會造成一長串程式碼的話，可以改用using來替代，達到精簡程式碼的目的。

程式碼

select firstName,lastName,city,state
from Person
left join Address
on Person.personId = Address.personId;

或

select firstName,lastName,city,state
from Person
left join Address
using(personId);

參考資料

DB學習筆記 — SQL刷題001

LINE 分享

小屋創作

日誌2024-10-30 02:19
【Leetcode】175. Combine Two Tables

相關創作

【Leetcode】184. Department Highest Salary

【Leetcode】511. Game Play Analysis I

【SQL】一些常用的SQL語法

留言

日誌2024-10-30 02:19【Leetcode】175. Combine Two Tables

相關創作

【Leetcode】184. Department Highest Salary

【Leetcode】511. Game Play Analysis I

【SQL】一些常用的SQL語法

留言

日誌2024-10-30 02:19
【Leetcode】175. Combine Two Tables