1083. 销售分析 II-sql

最新推荐文章于 2022-04-10 21:51:18 发布

HansmV

最新推荐文章于 2022-04-10 21:51:18 发布

阅读量221

点赞数

分类专栏： sql 文章标签： mysql sql 数据库

本文链接：https://blog.youkuaiyun.com/HansmV/article/details/117774991

版权

该SQL查询旨在找出只购买了S8但未购买iPhone的买家ID。通过左连接Sales和Product表，然后按buyer_id分组并计算每个产品的总数量，确保S8的数量大于0且iPhone的数量等于0。另一种方法是使用子查询来分别找出购买S8和iPhone的buyer_id，然后进行比较和排除。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- better one
select buyer_id   
from Sales left outer join Product on Sales.product_id = Product.product_id 
group by buyer_id having sum(product_name='S8')>0 and sum(product_name='iPhone')=0;


-- mine
select distinct buyer_id from Sales
where buyer_id in 
(select distinct buyer_id from Sales as s inner join Product as p on s.product_id = p.product_id where product_name = 'S8') 
and buyer_id not in 
(select distinct buyer_id from Sales as s inner join Product as p on s.product_id = p.product_id where product_name = 'iPhone') ;

-- better one

select buyer_id

from Sales left outer join Product on Sa