来源:力扣(LeetCode)链接:https://leetcode-cn.com/problems/sales-analysis-ii 【侵删】
ExampleA (销售分析 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 部手机。
解题思路(购买S8的用户中刨去购买iPhone的)
# 买s8的buyer_id
#select n.buyer_id from (select s.buyer_id,p.product_name from sales s join product p on p.product_id=s.product_id) n where n.product_name='S8';
# 买iphone的buyer_id
# select n.buyer_id from (select s.buyer_id,p.product_name from sales s join product p on p.product_id=s.product_id) n where n.product_name='iphone';
select distinct n.buyer_id from (select s.buyer_id,p.product_name from sales s join product p on p.product_id=s.product_id) n where n.product_name='S8' and n.buyer_id not in (select n1.buyer_id from (select s.buyer_id,p.product_name from sales s join product p on p.product_id=s.product_id) n1 where n1.product_name='iphone');
自己的思路还是不如别人的有特点
not in + 子连接 考点 逻辑清晰
select distinct s.buyer_id
from product p, sales s
where p.product_id = s.product_id
and s.buyer_id not in
(select s.buyer_id
from product p, sales s
where p.product_id = s.product_id
and p.product_name = 'iphone')
and p.product_name = 'S8'
用sum判断等式 符合值为1 不符合为0 很巧妙
select s.buyer_id
from product p, sales s
where p.product_id = s.product_id
group by s.buyer_id
having sum(p.product_name='S8') > 0 and sum(p.product_name='iphone') < 1