Leetcode # 1398. Customers Who Bought Products A and B but Not C

CREATE TABLE IF NOT EXISTS Orders
(
    order_id     int,
    customer_id  int,
    product_name varchar(30)
);
TRUNCATE TABLE Customers;
INSERT INTO
    Customers (customer_id, customer_name)
VALUES
    ('1', 'Daniel');
INSERT INTO
    Customers (customer_id, customer_name)
VALUES
    ('2', 'Diana');
INSERT INTO
    Customers (customer_id, customer_name)
VALUES
    ('3', 'Elizabeth');
INSERT INTO
    Customers (customer_id, customer_name)
VALUES
    ('4', 'Jhon');
TRUNCATE TABLE Orders;
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('10', '1', 'A');
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('20', '1', 'B');
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('30', '1', 'D');
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('40', '1', 'C');
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('50', '2', 'A');
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('60', '3', 'A');
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('70', '3', 'B');
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('80', '3', 'D');
INSERT INTO
    Orders (order_id, customer_id, product_name)
VALUES
    ('90', '4', 'C');
Customers
Column NameType    
customer_id        int    
customer_name      varchar

                                           customer_id is the primary key for this table.

Table: Orders

Orders
Column NameType    
order_id      int    
customer_id  int    
product_name  varchar

                                            order_id is the primary key for this table.

Write an SQL query to report the customer_id and customer_name of customers who bought products "A", and "B" but did not buy product "C" since we want to recommend them to purchase this product.

Return the result table ordered by customer_id.

The query result format is in the following example.

Example 1:

Input: 

Customers
customer_id customer_name
1Daniel        
2        Diana        
3Elizabeth    
4Jhon    



Orders table:

order_idcustomer_id  product_name  
10 1A
201B
301D
401C
502A
603A
703B
803D
904C


Output: 

customer_id customer_name
3Elizabeth    


Explanation: Only the customer_id with id 3 bought the product A and B but not the product C.

方法:

这个方法的关键之处在于"HAVING"后面的聚合函数

由于题目条件是先买完A产品和B产品才会被推荐C产品

所以SUM(IF())条件大于0可判断A产品和B产品并没有购买C产品

SELECT
    o.customer_id,
    c.customer_name
FROM
    Customers c
        RIGHT JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY
    o.customer_id, c.customer_name
HAVING
      SUM(IF(o.product_name = 'A', 1, 0)) > 0
  AND SUM(IF(o.product_name = 'B', 1, 0)) > 0
  AND SUM(IF(o.product_name = 'C', 1, 0)) = 0
;


Reference:力扣
 

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值