leetcode183 从不订购的客户 Customers Who Never Order

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

分享图片

 

 创建表和数据:

Create table If Not Exists Customers (Idint, Name varchar(255));
Create table If Not Exists Orders (Id int,CustomerId int);
Truncate table Customers;
insert into Customers (Id, Name) values(1, Joe);
insert into Customers (Id, Name) values(2, Henry);
insert into Customers (Id, Name) values(3, Sam);
insert into Customers (Id, Name) values(4, Max);
Truncate table Orders;
insert into Orders (Id, CustomerId) values(1, 3);
insert into Orders (Id, CustomerId) values(2, 1);

解法:

1.顾客表的id和订单表的customerid关联,得出的是买了的东西的顾客。用left join,没买东西的顾客,其对应的订单为空。这是一种求集合差的方法。

select C.name as Customers
from Customers as C left join Orders as O on (C.id = O.customerid)
where O.id is NULL;

先用子查询将买过东西的顾客id选出来。 在应用left join求集合差。

select C.name as `Customers`
from Customers as C left join (
    select distinct customerid
    from Orders
) as O on (C.id = O.customerid)
where O.customerid is NULL;

2.用not in也可以。 先用子查询将买过东西的顾客id选出来。 然后排除这些顾客的id即可。

select C.name as Customers
from Customers as C 
where C.id not in (
    select distinct customerid
    from Orders
) 

集合差定义:C=A-B。C中的元素等于在A中但是不在B中。因此,对A中的每个元素a,如果元素a不在B中,则元素a就是集合C的元素。

EXISTS是布尔运算符,常用于测试子查询。

SELECT select_list FROM a_table WHERE [NOT] EXISTS(subquery);

当subquery返回任何行时,EXISTS返回true,否则返回false。

select C.name as `Customers`
from Customers as C 
where not exists (
    select distinct customerid
    from Orders as O
    where O.customerid = C.id
) ;
相关文章
相关标签/搜索