购买“iPhone”的买家退货空空如也

tables:

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 谢谢

你需要过滤before聚合：

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