leetcode1083. 销售分析 II（SQL）

客官°小女子只卖身不卖艺 2023-07-07 08:26 56阅读 0赞

Table: Product

\+-------------+---------+  
| 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 部手机。

思路：查出两个手机的id，根据手机id查出买过两个手机的名单，判断每一个人在第一个名单里，不爱第二个名单里。

select distinct buyer_id
    from Sales
    where buyer_id in (select buyer_id from Sales where product_id=(select product_id from Product where product_name='S8')) 
    and buyer_id not in (select buyer_id from Sales where product_id=(select product_id from Product where product_name='iPhone'));