Leetcode # 183 Customers Who Never Order

Table:

Customers
Column NameType
idint
namevarchar

Create table If Not Exists Customer (id int, name varchar(255));
Create table If Not Exists Orders (id int, customerId int);
Truncate table Customer;
insert into Customer (id, name) values ('1', 'Joe');
insert into Customer (id, name) values ('2', 'Henry');
insert into Customer (id, name) values ('3', 'Sam');
insert into Customer (id, name) values ('4', 'Max');
Truncate table Orders;
insert into Orders (id, customerId) values ('1', '3');
insert into Orders (id, customerId) values ('2', '1');

id is the primary key column for this table.
Each row of this table indicates the ID and name of a customer.

Orders
Column NameType
id       int
customerId  int  

id is the primary key column for this table.
customerId is a foreign key of the ID from the Customers table.
Each row of this table indicates the ID of an order and the ID of the customer who ordered it.

 

Require:

Write an SQL query to report all customers who never order anything.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Customers table
idname
1Joe
2Henry
3Sam
4Max

Orders
idcustomerId
13
21

Output
Customers
Henry
Max      

SQL:

-- 本题注意 需要将column :name 改名为Customers

此外关键字为NOT in

逻辑是现将已经购买的商品的人筛出来然后使用关键函数NOT in把所选出来的筛掉就可返回正确结果.

SELECT customerid FROM orders;-- not in is satifor
SELECT name as Customers from Customer WHERE id NOT in (
SELECT customerid FROM orders);

用join方法实现的如下:

本解法中使用连接将没有购买的人筛出Null值然后使用is null函数 即可返回正确结果(cited by 猴子)

select a.Name as Customers
from Customers as a
left join Orders as b
on a.Id=b.CustomerId
where b.CustomerId is null;


Reference:力扣

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值