为有牺牲多壮志,敢教日月换新天。

[SQL]LeetCode183. 从不订购的客户 | Customers Who Never Order

★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
➤微信公众号:山青咏芝(shanqingyongzhi)
➤博客园地址:山青咏芝(https://www.cnblogs.com/strengthen/
➤GitHub地址:https://github.com/strengthen/LeetCode
➤原文地址:https://www.cnblogs.com/strengthen/p/9720985.html 
➤如果链接不是山青咏芝的博客园地址,则可能是爬取作者的文章。
➤原文已修改更新!强烈建议点击原文地址阅读!支持作者!支持原创!
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★

热烈欢迎,请直接点击!!!

进入博主App Store主页,下载使用各个作品!!!

注:博主将坚持每月上线一个新app!!!

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  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Table: Orders.

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

Using the above tables as example, return the following:

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。

Customers 表:

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders 表:

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

例如给定上述表格,你的查询应返回:

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

方法:使用子查询和NOT IN子句

算法:如果我们有一个订购过的客户列表,很容易知道谁从未订购过。

我们可以使用以下代码来获取此类列表。

select customerid from orders;

然后,我们可以NOT IN用来查询不在此列表中的客户。

1 select customers.name as 'Customers'
2 from customers
3 where customers.id not in
4 (
5     select customerid from orders
6 );

222ms

1 # Write your MySQL query statement below
2 SELECT Name as 'Customers' from Customers where Id not in (select CustomerId from Orders)

 

posted @ 2018-09-28 21:20  为敢技术  阅读(327)  评论(0编辑  收藏  举报