购买“iPhone"的买家返回空 [英] buyer who bought "iPhone" returns empty

问题描述

表格:

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"的买家，使用这个查询时我会得到空:

if I want to find the buyers who bought "iPhone", I get empty when using this query:

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"等其他商品的买家，我肯定可以使用相同的查询找到它:

if I wnat to find buyers who bought other items like "S8", I can definitely get it by using the same query:

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

what's wrong with iPhone? I use MariaDB 10.3 Thanks

推荐答案

你需要在聚合之前过滤:

You need to filter before aggregating:

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;

或者使用一个统计匹配的聚合函数:

Or use an aggregation function that counts the matches:

having sum(p.product_name = 'iPhone') > 0

这篇关于购买“iPhone"的买家返回空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！