LeetCode-1083. 销售分析 II

Table: Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
| unit_price   | int     |
+--------------+---------+
product_id 是这张表的主键
Table: Sales

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+------ ------+---------+
这个表没有主键，它可以有重复的行.
product_id 是 Product 表的外键.
编写一个 SQL 查询，查询购买了 S8 手机却没有购买 iPhone 的买家。注意这里 S8 和 iPhone 是 Product 表中的产品。

查询结果格式如下图表示：

Product table:
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1          | S8           | 1000       |
| 2          | G4           | 800        |
| 3          | iPhone       | 1400       |
+------------+--------------+------------+

Sales table:
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date  | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1         | 1          | 1        | 2019-01-21 | 2        | 2000  |
| 1         | 2          | 2        | 2019-02-17 | 1        | 800   |
| 2         | 1          | 3        | 2019-06-02 | 1        | 800   |
| 3         | 3          | 3        | 2019-05-13 | 2        | 2800  |
+-----------+------------+----------+------------+----------+-------+

Result table:
+-------------+
| buyer_id    |
+-------------+
| 1           |
+-------------+
id 为 1 的买家购买了一部 S8，但是却没有购买 iPhone，而 id 为 3 的买家却同时购买了这 2 部手机。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/sales-analysis-ii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

审题：查询购买了 S8 手机却没有购买 iPhone 的买家。需要根据产品表，id查询产品。

思考：先找买s8的人，再找买ipone的人，然后排除。

解题：

解法一

先找出买了S8的人

(
    select distinct buyer_id
    from Product as P join Sales as S
        on(P.product_id = S.product_id and P.product_name ='S8')
) as A

再找出买了iPhone的人

(
    select distinct buyer_id
    from Product as P join Sales as S
        on(P.product_id = S.product_id and P.product_name ='iPhone')
) as B

再从买了S8的人中排除掉买了iPhone的。

用left join求差集

select A.buyer_id
from
(
    select distinct buyer_id
    from Product as P join Sales as S
        on(P.product_id = S.product_id and P.product_name ='S8')
) as A
left join
(
    select distinct buyer_id
    from Product as P join Sales as S
        on(P.product_id = S.product_id and P.product_name ='iPhone')
) as B
    on(A.buyer_id = B.buyer_id)
where B.buyer_id is NULL

解法二

对每个买家，统计其买S8的总数量A和iphone的总数量B。

选出A大于0但是B等于0的买家。

要连接销售表和产品表。但并不是每个产品都会被用户买。因此要用left join。

其次要按买家分组，计算A和B，选出A>0且B=0的买家。

-- 练习
select S.buyer_id from Sales as S left join Product as P
on(P.product_id = S.product_id)
group by S.buyer_id
-- s8大于零，iphone等于零
having  sum(if(P.product_name ='S8',1,0)) > 0 and sum(if(P.product_name ='iPhone',1,0)) =0




select S.buyer_id
from Sales as S left join Product as P 
    on(P.product_id = S.product_id)
group by S.buyer_id
having  sum(if(P.product_name ='S8',1,0)) > 0 and sum(if(P.product_name ='iPhone',1,0)) =0

知识点：