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 Name	Type
customer_id	int
customer_name	varchar

                                           customer_id is the primary key for this table.

Table: Orders

Orders

Column Name	Type
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
1	Daniel
2	Diana
3	Elizabeth
4	Jhon

Orders table:

order_id	customer_id	product_name
10	1	A
20	1	B
30	1	D
40	1	C
50	2	A
60	3	A
70	3	B
80	3	D
90	4	C

Output: 

customer_id	customer_name
3	Elizabeth

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：力扣