今天的主题是合并呢，感觉好像会用到 UNION。

175. Combine Two Tables

Analysis

将两个表连接，进行组合查询。

Code

1
2
3

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

1581. Customer Who Visited but Did Not Make Any Transactions

Analysis

找出只出现在 Visits 内而不出现在 Transactions 内的 visit_id，同时相同 customer_id 的个数。

Code

method 1

可以使用 GROUP BY 来将两个查询的结果组合起来。

select customer_id, count(customer_id) as count_no_trans
from Visits
where Visits.visit_id not in (select visit_id from Transactions)
group by customer_id

method 2

也可以连接查询在组合。

select customer_id, count(customer_id) as count_no_trans
from Visits left join Transactions
on Visits.visit_id = Transactions.visit_id
where amount is null
group by customer_id

1148. Article Views I

Analysis

找出自己看过自己文章的作者，重复出现的，只列出 1 次记录，感觉这个今天最简单的题了。

Code

select distinct viewer_id as id
from Views
where Views.author_id = Views.viewer_id
order by id

Summary

还是不熟悉啊。