mysql - 购买“iPhone”的买家退货为空
问题描述
表:
create table product (
product_id int,
product_name varchar(50),
unit_price int);
insert into product values
(1, "S8", 1000),
(2, "G4", 800),
(3, "iPhone", 1400);
create table sales (
seller_id int,
product_id int,
buyer_id int,
sale_date date,
quantity int,
price int);
insert into sales values
(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);
如果我想查找购买了“iPhone”的买家,使用此查询时会出现空白:
select s.buyer_id, p.product_name from sales s join product p
on s.product_id = p.product_id
group by s.buyer_id
having p.product_name = "iPhone";
如果我想找到购买了“S8”等其他商品的买家,我肯定可以通过使用相同的查询得到它:
select s.buyer_id, p.product_name from sales s join product p
on s.product_id = p.product_id
group by s.buyer_id
having p.product_name = "S8";
结果符合预期:
buyer_id | productname
1 | S8
3 | S8
iPhone有什么问题?我使用 MariaDB 10.3 谢谢
解决方案
您需要在聚合之前进行过滤:
select s.buyer_id
from sales s join
product p
on s.product_id = p.product_id
where p.product_name = 'iPhone'
group by s.buyer_id;
或者使用计算匹配的聚合函数:
having sum(p.product_name = 'iPhone') > 0
推荐阅读
- python - 使用数组索引训练网络(tensorflow)
- c# - 在两个值之间获取 n 个不同的随机数,其和等于给定数
- python - django:无法通过单击提交按钮调用 python 函数
- python - how to add add new row for each distinct ID in pandas?
- eclipse - 缺少工件 com.google.appengine:appengine-tools-sdk:jar:${appengine.sdk.version}
- python - How To Edit ManyToManyField In Django Admin list Display Page?
- roku - Use LabelList or MarkupList to render two labels for each content node?
- python - Pandas groupby find common strings
- c# - How to get ApplicationDbContext instance in AccountController for ASP .NET Core 2.0 With Individual Authentication?
- selenium - how to execute Cucumber Step defination with TestNG annotation