只显示子查询中的1列:列出bran_name，它的平均价格是>= 3，并且至少有2种不同的产品

Question

以下是我的疑问：

select avg(p2.price), p2.brand_name, count(distinct p2.product_id) 
count_of_products
from product p2
group by p2.brand_name
having count(distinct p2.product_id) >= 2 and avg(p2.price) > 3

但是我需要在最后的查询中只显示band_name。我试过这个：

select p1.brand_name from product p1,
(select avg(p2.price), p2.brand_name, count(distinct p2.product_id) 
count_of_products
from product p2
group by p2.brand_name
having count(distinct p2.product_id) >= 2 ) p2 and and avg(p2.price) > 3
where 
p1.brand_name = p2.brand_name

但它给了我以下错误：

>[Error] Script lines: 3-9 --------------------------
 No column name was specified for column 1 of 'p2'.

Answer 1

，但我需要在最后的查询中只显示band_name

因此，只需从select子句中删除其他列：

select brand_name
from product
group by brand_name
having count(distinct product_id) >= 2 and avg(price) > 3