您可以使用窗口函数rank根据平均值标记排名，然后过滤以获得排名最高的行：

select *
from (
    select lgproduct.brand_id,
        brand_name,
        brand_type,
        round(avg(prod_price), 2),
        rank() over (
            order by round(avg(prod_price), 2) desc
            ) as rnk
    from boff.lgproduct,
        boff.lgbrand
    where lgproduct.brand_id = lgbrand.brand_id
    group by lgproduct.brand_id,
        brand_name,
        brand_type
    ) t
where rnk = 1
order by brand_id;

如果您必须使用having，请尝试：

select lgproduct.brand_id,
    brand_name,
    brand_type,
    round(avg(prod_price), 2)
from boff.lgproduct,
    boff.lgbrand
where lgproduct.brand_id = lgbrand.brand_id
group by lgproduct.brand_id,
    brand_name,
    brand_type
having round(avg(prod_price), 2) = (
        select max(round(avg(prod_price), 2))
        from boff.lgproduct,
            boff.lgbrand
        where lgproduct.brand_id = lgbrand.brand_id
        group by lgproduct.brand_id,
            brand_name,
            brand_type
        )
order by lgproduct.brand_id;

您会得到错误，因为您不能在SQL中嵌套聚合函数。
使用此命令获取带有max_avg_prod_price的品牌，而不是：

select  bb.brand_ID,    bb.brand_name,  bb.brand_type,  round((avg(pp.prod_price)),2)   as  MaxPrice
                from    BOFF.lgbrand    bb, BOFF.lgproduct  pp
                where   pp.brand_ID =   bb.brand_ID
                group   by  brand_ID
                order by MaxPrice desc
                limit 1;