Leetcode Mysql 1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客（DAY 7）

蔚落 2022-09-14 06:29 166阅读 0赞

### 文章目录 ###

*   *  原题题目
     *  代码实现（首刷自解）
     *  代码实现（首刷优化）

--------------------

## 原题题目 ##

--------------------

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATG92ZSA2_size_20_color_FFFFFF_t_70_g_se_x_16]

--------------------

## 代码实现（首刷自解） ##

--------------------

# Write your MySQL query statement below
    SELECT
    DISTINCT 
        c.customer_id,
        c.customer_name
    FROM
        customers c
    JOIN 
        orders o
    WHERE
        c.customer_id = o.customer_id
        and  exists (SELECT * FROM orders WHERE product_name = 'A' and customer_id = o.customer_id)
        and  exists (SELECT * FROM orders WHERE product_name = 'B' and customer_id = o.customer_id)
        and  not exists(SELECT * FROM orders WHERE product_name = 'C' and customer_id = o.customer_id)

--------------------

## 代码实现（首刷优化） ##

--------------------

# Write your MySQL query statement below
    SELECT
    DISTINCT 
        c.customer_id,
        c.customer_name
    FROM
        customers c JOIN orders o on c.customer_id = o.customer_id
    GROUP BY
        o.customer_id
    HAVING
        SUM(product_name = 'A') > 0 
        and SUM(product_name = 'B') > 0 
        and SUM(product_name = 'C') = 0

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATG92ZSA2_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/0278db450c24445c89b51bbec6b3e893.png